SUMIF WITH OR
 

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!

SUMIF WITH OR
 

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
"Lea from CA" wrote in message
...
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!

SUMIF WITH OR
 

One way...

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

--
Biff
Microsoft Excel MVP


"Lea from CA" wrote in message
...
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!

SUMIF WITH OR
 

Hi Biff
Great shut,I forgot, they could be group that way, makes it much neater.
Have a good evening.
John
"T. Valko" wrote in message
...
One way...

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

--
Biff
Microsoft Excel MVP


"Lea from CA" wrote in message
...
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!

SUMIF WITH OR
 

Works great! Thank you!

"T. Valko" wrote:

One way...

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

--
Biff
Microsoft Excel MVP


"Lea from CA" wrote in message
...
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!


.

SUMIF WITH OR
 

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

"John" wrote:

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
"Lea from CA" wrote in message
...
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!

.

SUMIF WITH OR
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Lea from CA" wrote in message
...
Works great! Thank you!

"T. Valko" wrote:

One way...

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

--
Biff
Microsoft Excel MVP


"Lea from CA" wrote in message
...
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!


.