SUMIF WITH OR
in [General]
|
Prev: Time Formular
Next: sum across worksheets conditionally
From: Lea from CA on 18 Mar 2010 12:18 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(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 18 Mar 2010 16:51
You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Lea from CA" <LeafromCA(a)discussions.microsoft.com> wrote in message news:F159D503-3EA7-44EE-8DA0-A520B84A4121(a)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(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! >> >> >> . >> |