Prev: Time Formular
Next: sum across worksheets conditionally
From: Lea from CA on 17 Mar 2010 19:41
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!
From: John on 17 Mar 2010 20:15
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(a)discussions.microsoft.com> wrote in message
news:FEF16398-97F6-4708-B853-30AA1415D8C9(a)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!

From: T. Valko on 17 Mar 2010 20:27
One way...

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

--
Biff
Microsoft Excel MVP


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


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

From: Lea from CA on 18 Mar 2010 12:17
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(a)discussions.microsoft.com> wrote in message
> news:FEF16398-97F6-4708-B853-30AA1415D8C9(a)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!
>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Time Formular
Next: sum across worksheets conditionally