Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SUMIF function for multiple columns

Status
Not open for further replies.

Salut39

Technical User
Aug 2, 2006
178
GB
Hi Everyone,
I was wondering if it's possible to use Sumif function for multiple columns. Example:
=SUMIF($B:$B,A1,$C:$F).
It doesn't seem to work. Is there any other way to do it?
I want to calculate 12months worth of data using specified criteria.

Thanks in advance.
Yuri
 
multiple criteria or just summing multiple columns for a single criteria ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Multiple column to sum and multiple criteria, but criteria in one column so it's ok.
 
if you have multiple criteria in 1 column then you will need to do a SUMIF for each column and for each criteria e.g.

=SUMIF($B:$B,A1,$C:$C) + SUMIF($B:$B,A1,$D:$D) + SUMIF($B:$B,A1,$E:$E) + SUMIF($B:$B,A1,$F:$F)

If you then want to add another criteria you would use:

=SUMIF($B:$B,A1,$C:$C) + SUMIF($B:$B,A1,$D:$D) + SUMIF($B:$B,A2,$E:$E) + SUMIF($B:$B,A1,$F:$F) + SUMIF($B:$B,A2,$C:$C) + SUMIF($B:$B,A2,$D:$D) + SUMIF($B:$B,A2,$E:$E) + SUMIF($B:$B,A2,$F:$F)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Not that I'm bored or anything but I'm still trying to get my head fully around SUMPRODUCT so I came up with this

=SUMPRODUCT(((A1:A25="A")+(A1:A25="C"))*(B1:B25+C1:C25))

Where A1:A25 contains the criteria to test and the same rows in columns B & C contain the data.

However I've just messed around a little more and (insert line from Black Adder pertaining to fish forks) doesn't this work just the same

=SUMPRODUCT(((A1:A25="A")+(A1:A25="C"))*(B1:C25))

And the best bit? I still don't rreally understand how/why!!!! And yes, I've just been looking on xlDynamic.com

;-)
If a man says something and there are no women there to hear him, is he still wrong? [ponder]
How do I get the best answers?
 
Thanks but I was more thinking of applying this in one sumif.
 
It looks like it's working! Thanks Loomah.
 
Actually, thinking about this, don't use my second suggestion as it contravenes one of the basic rules of SUMPRODUCT, ie "All arrays must be the same size"

It seems to work ok for me but because of the array size I really don't see how so probably best avaoid it unless someone can explain more fully than I can!

;-)
If a man says something and there are no women there to hear him, is he still wrong? [ponder]
How do I get the best answers?
 
And just for the sheer hell of it....

=SUMPRODUCT((A1:A25={"A","C"})*(B1:B25+C1:C25))

;-)
If a man says something and there are no women there to hear him, is he still wrong? [ponder]
How do I get the best answers?
 
I think you've just replaced Sum(b1:c25):)

I think =SUMPRODUCT(((A1:A25="A")+(A1:A25="C"))*(B1:C25))
this one is working too. and i like it more.

Thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top