sumif with 'OR'
I would like to add up columns that meet 1 criteria from column 1 and that
also meet certain criteria from column B. A B C 212 31A 20 304 31A 20 212 31B 20 212 31C 10 304 31B 20 For e.g. is there some way I can add up all the Amounts for 212 (from Column A) that are either 31A or 31B (from column B) returning back an answer that sums the relevant figures from column C? If you have an answer, it will be much appreciated; I've beens scratching my head with this one for a while! Thanks, Amanda |
sumif with 'OR'
One way:
=SUMPRODUCT(--(A1:A10=212),--(((B1:B10="31a")+(B1:B10="31b"))0),C1:C10) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html A Heeps wrote: I would like to add up columns that meet 1 criteria from column 1 and that also meet certain criteria from column B. A B C 212 31A 20 304 31A 20 212 31B 20 212 31C 10 304 31B 20 For e.g. is there some way I can add up all the Amounts for 212 (from Column A) that are either 31A or 31B (from column B) returning back an answer that sums the relevant figures from column C? If you have an answer, it will be much appreciated; I've beens scratching my head with this one for a while! Thanks, Amanda -- Dave Peterson |
sumif with 'OR'
Try something like this:
Using your posted data list... F2: 212 G2: 31A G3: 31B This formula returns the sum of Col_C items where the corresponding Col_A value equals 212 AND the corresponding Col_B value equals 31A OR 31B H2: =SUMPRODUCT((A2:A10=F2)*((B2:B10=G2)+(B2:B10=G3))* C2:C10) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "A Heeps" wrote: I would like to add up columns that meet 1 criteria from column 1 and that also meet certain criteria from column B. A B C 212 31A 20 304 31A 20 212 31B 20 212 31C 10 304 31B 20 For e.g. is there some way I can add up all the Amounts for 212 (from Column A) that are either 31A or 31B (from column B) returning back an answer that sums the relevant figures from column C? If you have an answer, it will be much appreciated; I've beens scratching my head with this one for a while! Thanks, Amanda |
sumif with 'OR'
sumproduct() will work
= sumproduct(--(A1:A1000=212),--(or(B1:B1000="31A",B1:B1000="31B)),C1:C1000) the "--(" changes the logical true false to a numeric 1 0 you cannot reference an entire column in Sumproduct (A:A wont work) all of the arrays have to be the same size "A Heeps" wrote: I would like to add up columns that meet 1 criteria from column 1 and that also meet certain criteria from column B. A B C 212 31A 20 304 31A 20 212 31B 20 212 31C 10 304 31B 20 For e.g. is there some way I can add up all the Amounts for 212 (from Column A) that are either 31A or 31B (from column B) returning back an answer that sums the relevant figures from column C? If you have an answer, it will be much appreciated; I've beens scratching my head with this one for a while! Thanks, Amanda |
sumif with 'OR'
=SUMPRODUCT((A1:A5=212)*(B1:B5={"31A","31B"})*C1:C 5)
"A Heeps" wrote: I would like to add up columns that meet 1 criteria from column 1 and that also meet certain criteria from column B. A B C 212 31A 20 304 31A 20 212 31B 20 212 31C 10 304 31B 20 For e.g. is there some way I can add up all the Amounts for 212 (from Column A) that are either 31A or 31B (from column B) returning back an answer that sums the relevant figures from column C? If you have an answer, it will be much appreciated; I've beens scratching my head with this one for a while! Thanks, Amanda |
All times are GMT +1. The time now is 10:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com