sumif with 2 criteria?

[Wombat]

I want to add up some turnover stats in column E but sort the numbers
according to two criteria held in columns A and D

If there was only the one I would do this with a simple sumif formula but
I'm not too sure how to go about it with two separate criteria...

Any help?

 

[Per Jessen]

Hi

If you are using xl2007, you can use =Sumifs() function, else you have to
use a SumProduct formula.

Supply us with some more information if you need help writing the formula.

Regards,
Per

 

[Mike H]

Hi,

=SUMPRODUCT((A1:A10="1st Criteria")*(D110="2nd Criteria")*(E1:E10))

Mike

 

[Ms-Exl-Learner]

=SUMPRODUCT((A1:A100="YOUR A COLUMN CRITERIA")*(D1100="YOUR D COLUMN
CRITERIA")*E1:E100)

Sample:-
=SUMPRODUCT((A1:A100="A")*(D1100="D")*E1:E100)


Remember to Click Yes, if this post helps!

 

[Wombat]

Thanks for your answers. I think I get the principle of the formula but I get
a #NAME error when I try it...

=sumproduct((C18:C31=a)*(D1831=q)*(E18:E31))

My version of Excel (2003 in German!) seems to want semi-colons instead of *
symbols. Does this make a difference?

Advice?

 

[Mike H]

Hi,

In you example formula if you are searching for "a" then you must put it in
quotes, if you are searching for number then you don't use quotes.

so

=sumproduct((C18:C31="a")*(D1831="q")*(E18:E31))


or

=sumproduct((C18:C31=99)*(D1831=100)*(E18:E31))


or better still use cell references for the criteria

=sumproduct((C18:C31=A1)*(D1831=B1)*(E18:E31))


Mike

 

[Wombat]

This is what my formula looks like:

=sumproduct((Tabelle3!$B:$B=Tabelle1!$C6)*(Tabelle3!$C:$C=Tabelle1!E$5)*(Tabelle3!$D:$D))

"Tabelle1!$C6" is a 6 digit number which has been converted to a text format
"Tabelle1!E$5" is a word
"Tabelle3!$D:$D" this contains the turnover (currency)

So far, its still coming back with a number error...

 

[Mike H]

Hi,

You can't use full columns unless you are using Excel 2007 so shorten the
ranges

=SUMPRODUCT((Tabelle3!B1:B20=Tabelle1!$C6)*(Tabelle3!C1:C20=Tabelle1!E$5)*(Tabelle3!D120))

Mike

 

[David Biddulph]

In Excel 2003 you can't use a full column reference.
Change $B:$B to $B2:$B100 or an appropriate range.

 

[Per Jessen]

Hi

You can not use the entire column as reference before xl2007, so change your
formula like this:

=SUMPRODUCT(--(Tabelle3!$B1:$B1000=Tabelle1!$C6);--(Tabelle3!$C1:$C1000=Tabelle1!E$5);--(Tabelle3!$D1:$D1000))

Just remeber that all ranges has to have same size.

Regards,
Per

 

[Wombat]

It works!!

I just had a little victory dance to celebrate and scared a colleague!

Thanks a lot

 

[Jacob Skaria]

Great. Just for your information; in XL2003 you can refer as below incase
your data can run down to any number of cells. 65535 is just one row less
than the max number of rows; but keep in mind that SUMPRODUCT() formula is
going to be slower with the increase in range..

=SUMPRODUCT((Tabelle3!$B1:$B65535=Tabelle1!$C6)*
(Tabelle3!$C1:$C65535=Tabelle1!E$5)*(Tabelle3!$D1:$D65535))