SUMIF function for multiple columns

[Salut39]

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

 

[xlbo]

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

 

[Salut39]

Multiple column to sum and multiple criteria, but criteria in one column so it's ok.

 

[xlbo]

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

 

[Loomah]

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?
How do I get the best answers?​

 

[Salut39]

Thanks but I was more thinking of applying this in one sumif.

 

[Salut39]

It looks like it's working! Thanks Loomah.

 

[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?
How do I get the best answers?​

 

[Loomah]

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?
How do I get the best answers?​

 

[Salut39]

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