SUMIF WITH OR

[Lea from CA]

I have a table with 3 columns of data - Column A Store Number, Column B Dept
& Column C Amount. There are several store numbers and 10 distinct Depts
(Dept A - J. I want a sum of amounts where store# = 1 and dept is Dept A,
Dept B and Dept C.

Any help will be greatly appreciated.

Thanks!

 

[John]

Hi Lea
This should do it, its a bit long but it works;
=SUMPRODUCT(--(A2:A21="Store 1"),--(B2:B21="Dept
A"),(C2:C21))+SUMPRODUCT(--(A2:A21="Store 1"),--(B2:B21="Dept
B"),(C2:C21)+SUMPRODUCT(--(A2:A21="Store 1"),--(B2:B21="Dept C"),(C2:C21)))
This formula goes all in one cell, adjust range to your needs and make sure that
the Store and Dept are exactly spelled the same way as what's in your table.
HTH
John

 

[T. Valko]

One way...

=SUMPRODUCT((A1:A20=1)*(B1:B20={"A","B","C"})*C1:C20)

 

[John]

Hi Biff
Great shut,I forgot, they could be group that way, makes it much neater.
Have a good evening.
John

 

[Lea from CA]

Works great! Thank you!

One way...

=SUMPRODUCT((A1:A20=1)*(B1:B20={"A","B","C"})*C1:C20)

--
Biff
Microsoft Excel MVP





.

 

[Lea from CA]

Thank you! This works too but like you said the other way is neater!

 

[T. Valko]

You're welcome. Thanks for the feedback!