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" 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" 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" 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" 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" 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 FormularNext: sum across worksheets conditionally