SUMIF and multiple criteria
SUMIF and multiple criteria
(OP)
Is it possible to write a SUMIF equation in excel based on multiple criteria being true?
Say, for instance, I have a name in column A, a date in column B, and a value in column C. I know I can use SUMIF to add the values for each name or each date, but what about a name on a given date? I'd like to avoid the creation of a column D with name and date concatenated. I know the concatenated column solution will work, but wonder if there is a more elegant solution.
Say, for instance, I have a name in column A, a date in column B, and a value in column C. I know I can use SUMIF to add the values for each name or each date, but what about a name on a given date? I'd like to avoid the creation of a column D with name and date concatenated. I know the concatenated column solution will work, but wonder if there is a more elegant solution.





RE: SUMIF and multiple criteria
Also the sumif takes a "criteria" argument. In the case of filtering, the criteria argument can point to cells containing multiple conditions... maybe you can use the same trick for sumif.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: SUMIF and multiple criteria
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: SUMIF and multiple criteria
a b 1
b b 2
c b 3
I'll test for column A is "b" and column B is "b"
Create your helper column D with the formula =AND(A1="b",B1="B") and copy down.
This will result in Boolean TRUE or FALSE values in column D
Where you want your SUMIF enter
=SUM(C1:C3*D1:D3) as an ARRAY FORMULA by pressing CTRL+SHIFT+ENTER
RE: SUMIF and multiple criteria
In XL2007:
=SUMIFS(A6:A12,D6:D12,">10",E6:E12,">60")
In earlier versions :
=SUMPRODUCT(A6:A12,--(D6:D12>10),--(E6:E12>60))
In the sumproduct version the -- is required so that the criteria are checked for each row (returning either 1 or 0) before the product.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: SUMIF and multiple criteria
RE: SUMIF and multiple criteria
An excellent description of the potential power of the SUMPRODUCT function is (was?) provided at URL
www.xldynamic.com/source/xld.SUMPRODUCT.html
but when I tested that site a few minutes ago it seems to have lapsed. However it is still (right now) in Google's cache, so if you search for "xldynamic sumproduct" (without the quotes) then take the "cached" option you can still get hold of it.
RE: SUMIF and multiple criteria
Column A has names (A2:A200)
Column B has dates (B2:B200)
Column C has corresponding values
In cell A210 I type in a name
In cell B210 I type in a date
In cell C210 I type in;
=SUMIF(A2:A200,A210&B210,C2:C200)
Oddly enough it works !
Say I have cubic ft in Col A,
and Concrete in Col B, Qty in Col C.
I may have separate pours in Rows 2 thru 200,
and this neat formula gives me the grand total.
Isn't Excel amazing !
Good Luck
Ralf
RE: SUMIF and multiple criteria
I was trying to use the conditional sum wizard in excel this morning and could not figure out what the {} were. It worked when they were there but they would disappear when I made any changes. Now I know that they are an array formulas and how to keep them.
For anyone that can't find the conditional sum wizard it is an add-in and may have to be installed from the add-ins menu.
RE: SUMIF and multiple criteria
Best regards
Morten