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!

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" <LeafromCA@discussions.microsoft.com> wrote in message 
news:FEF16398-97F6-4708-B853-30AA1415D8C9@microsoft.com...
>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! 

One way...

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

-- 
Biff
Microsoft Excel MVP


"Lea from CA" <LeafromCA@discussions.microsoft.com> wrote in message 
news:FEF16398-97F6-4708-B853-30AA1415D8C9@microsoft.com...
>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! 

Hi Biff
Great shut,I forgot, they could be group that way, makes it much neater.
Have a good evening.
John
"T. Valko" <biffinpitt@comcast.net> wrote in message 
news:OhTQSHjxKHA.812@TK2MSFTNGP06.phx.gbl...
> One way...
>
> =SUMPRODUCT((A1:A20=1)*(B1:B20={"A","B","C"})*C1:C20)
>
> -- 
> Biff
> Microsoft Excel MVP
>
>
> "Lea from CA" <LeafromCA@discussions.microsoft.com> wrote in message 
> news:FEF16398-97F6-4708-B853-30AA1415D8C9@microsoft.com...
>>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!
>
> 

Works great!  Thank you!

"T. Valko" wrote:

> One way...
> 
> =SUMPRODUCT((A1:A20=1)*(B1:B20={"A","B","C"})*C1:C20)
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Lea from CA" <LeafromCA@discussions.microsoft.com> wrote in message 
> news:FEF16398-97F6-4708-B853-30AA1415D8C9@microsoft.com...
> >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! 
> 
> 
> .
> 

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" <LeafromCA@discussions.microsoft.com> wrote in message 
> news:FEF16398-97F6-4708-B853-30AA1415D8C9@microsoft.com...
> >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! 
> 
> .
> 

You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"Lea from CA" <LeafromCA@discussions.microsoft.com> wrote in message 
news:F159D503-3EA7-44EE-8DA0-A520B84A4121@microsoft.com...
> Works great!  Thank you!
>
> "T. Valko" wrote:
>
>> One way...
>>
>> =SUMPRODUCT((A1:A20=1)*(B1:B20={"A","B","C"})*C1:C20)
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Lea from CA" <LeafromCA@discussions.microsoft.com> wrote in message
>> news:FEF16398-97F6-4708-B853-30AA1415D8C9@microsoft.com...
>> >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!
>>
>>
>> .
>>