Sumif with multiple criteria with an extra twist

[Guest]

In Excel 2003, I need to sum some information with multiple criteria, but
there is an extra spin on it. Here is my data:

Transaction Amount
Category Customer Jan Feb Mar Apr YTD
Pizza A 20 15 23 39 97
B 15 12 9 10 46
C 25 25 30 10 90
Drinks A 10 5 8 12 35
B 10 8 10 10 38
C 15 20 12 19 66

I need to create a report that SUMs the total amount spent (regardless of
whether it is pizza or drinks) by Customer A in January (or any other month
that I specify). Essentially, I have two criteria - one in a row and one in
a column. Is this possible?

 

[T. Valko]

Try this:

A15 = customer
B15 = month

=SUMIF(B2:B7,A15,INDEX(C2:F7,,MATCH(B15,C1:F1,0)))

 

[Guest]

Hi,

Yes that's possible
=SUMPRODUCT((B2:B7=H1)*(C1:F1=G1)*(C2:F7))

A B C D E
F G H
Category Cus Jan Feb Mar Apr Jan A
Pizza A 8 7 6 5
B 8 4 8 6
C 9 5 9 7
Drinks A 10 6 10 8
B 11 7 11 9
C 12 8 12 12


mIKE

 

[Bob Phillips]

=SUMPRODUCT((B2:B7="A")*(MONTH(C1:F1)=1)*(C2:F7))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

This works. Thank you for your help!

Hi,

Yes that's possible
=SUMPRODUCT((B2:B7=H1)*(C1:F1=G1)*(C2:F7))

A B C D E
F G H
Category Cus Jan Feb Mar Apr Jan A
Pizza A 8 7 6 5
B 8 4 8 6
C 9 5 9 7
Drinks A 10 6 10 8
B 11 7 11 9
C 12 8 12 12


mIKE

 

[Guest]

This works great, but oddly enough the customer value of "C" does not work.
If I change that same customer to another letter, it works just fine. Weird.

But now I have another question. Is there a way to count how many products
Customer A bought in a certain month? With this small of a data set, it is
easy to count by hand, but my data will end up being an indefinite length by
year end.

Thanks.

 

[Guest]

Nevermind. I did get C to work.

This works great, but oddly enough the customer value of "C" does not work.
If I change that same customer to another letter, it works just fine. Weird.

But now I have another question. Is there a way to count how many products
Customer A bought in a certain month? With this small of a data set, it is
easy to count by hand, but my data will end up being an indefinite length by
year end.

Thanks.

 

[Guest]

Nevermind on my second question as well. I got it. Thanks again.