Exel formula help please

fracster

fracster

I love BRASH and BRASH loves me
Suspended / Banned
Messages
16,666
Name
Ade
Edit My Images
Yes
On the attached shot. Is there a formula( put in J1 for example)that will look at CTs name in column B and then add up the figures in the row where CTs name is in column D?

I seem to recall that there is but cannot remember the formula for the life off me.....:thinking:



exel1.jpg
 
Last edited:
=sumif(B3:B22,"CT",D3: D22)

without the space between the ":" and "D" as it gave me a smiley!
 
Last edited:
f1charlie said:
=sumif(B3:B22,"CT",D3: D22)

without the space between the ":" and "D" as it gave me a smiley!
Click to expand...

Thanks a bunch ..................:thumbs:

Should have asked on here yesterday morning....:bang:............:lol:
 
f1charlie said:
=sumif(B3:B22,"CT",D3: D22)

without the space between the ":" and "D" as it gave me a smiley!
Click to expand...

Or another, slightly more flexible, solution, would be

=SUM(IF(B3:B22="CT", D3: D22))

(again without the space afer the smiley!)

This is an array formula and you must enter it by pressing Ctrl+Shift+Enter instead of just Enter. (Also do that every time you edit it.) The reason it's a better solution is that the thing you're matching could be anything, including a cell reference or a formula, whereas the simple SUMIF construction is limited to matching hard-coded values.
 
excuse me but......

is anyone here using Open Office spreadsheets..?

thanks
 
StewartR said:
The reason it's a better solution is that the thing you're matching could be anything, including a cell reference or a formula, whereas the simple SUMIF construction is limited to matching hard-coded values.
Click to expand...

I don't think SUMIF is limited to hard-coded values so I don't understand what you mean here. :thinking:

I admit I don't really understand array formula so I don't use them and I think they're a bit dangerous as it's too easy to forget the CTRL+SHFT+ENTER and muck it up!


Yardbent said:
excuse me but......

is anyone here using Open Office spreadsheets..?

thanks
Click to expand...

No I don't, but what's the problem?
 
Last edited:
f1charlie said:
=sumif(B3:B22,"CT",D3: D22)

without the space between the ":" and "D" as it gave me a smiley!
Click to expand...

a better formula is =sumif(B:B,"CT",D: D) this will allow the range to be expanded without changing the formula. it is definately worth looking into pivot tables they are more poweful. if you change the formula to =sumif(B:B,F1,D: D) putting the search in cell F1 or you can change the F1 to another cell. would mean you could sum anything in the list.


Cheers Steve
 
Last edited:
Good to get some differing opinions and advice.thanks all.

Trying to think of something witty for Fabs question, can`t ,just some random names and numbers...........:(
 
You must log in or register to reply here.
Back
Top