sum a column based on multiple other columns being true

[sofia]

I am trying to sum amounts based on meeting multiple criteria in other
colums. I saw the response to the "contruction guy" posted here, but it does
not work for my information?

Month Client Amt
Jul Smith 5
Jul Smith 2
Aug Smith 3
Aug Scott 10

I want to calculate total amount per client each month using "sum" and "if"
statements.

Thank you,

sofia

 

[Bob Phillips]

=SUMPRODUCT(--(A2:A20="Jul"),--(B2_B20="Smith"),C2:C20)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Ken]

Sofia

You can use a multiple criteria sum function (array function) like:

{=SUM(($A$2:$A$5="Jul")*($B$2:$B$5="Smith")*$C$2:$C$5)}

This is entered as an array function (shift-ctl-enter). Athough, a
sumif function looks intuitively like something that should work, it
does not work with multiple criteria as you a have.

Good luck.

Ken
Norfolk, Va

 

[Tom Hutchins]

Why not use a pivot table? Put the Month as a page field or row field, put
the Client as a row field, and put the Amt as the data field.

If you don't want to use a pivot table, you will have to set up a separate
formula for each combination of criteria:

=SUMPRODUCT(--(A2:A5="Jul"),--(B2:B5="Smith"),C2:C5)
=SUMPRODUCT(--(A2:A5="Aug"),--(B2:B5="Smith"),C2:C5) etc.

Hope this helps,

Hutch

 

[sofia]

Wow, you are great! I want to use this number in a separate worksheet so I
rather have the formula than a pivot table, but I will try both ways to see
what works better for me.

Thank you,

sofia