SUMIF and multiple criteria 1

[davidbeach]

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.

 

[electricpete]

You can use something called the "conditional sum wizard".

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.

 

[electricpete]

An interesting alternative from the "help"

Create a total based on multiple conditions
Use the following formula to calculate the total value of cells F5:F25, where B5:B25 contains "Northwind" and the range C5:C25 contains the region name "Western".

=SUM(IF((B5:B25="Northwind")*(C5:C25="Western"),F5:F25))

To calculate the total value of cells F5:F25, where B5:B25 contains either "Northwind" or "Terra Firm", use the following formula.

=SUM(IF((B5:B25="Northwind")+(B5:B25="Terra Firm"),F5:F25))

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[MintJulep]

My take:

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*D13) as an ARRAY FORMULA by pressing CTRL+SHIFT+ENTER

 

[IDS]

Two alternatives without a "helper" column:

In XL2007:

=SUMIFS(A6:A12,D612,">10",E6:E12,">60")


In earlier versions :

=SUMPRODUCT(A6:A12,--(D612>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/

 

[davidbeach]

Thanks all. I decided to go with the helper column approach so that others using the file might have a better understanding of what is going on.

 

[Denial]

Doug's suggestion of SUMPRODUCT is worth investigating. The approach can can be used to achieve inclusion criteria containing complicated combinations of AND and OR logic.

An excellent description of the potential power of the SUMPRODUCT function is (was?) provided at URL

http://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.

 

[Cilli]

re: SUMIF with multiple conditions;
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

 

[HDS]

Great thread

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.

 

[MortenA]

A pivot table could maybe also be the thing. Its often overlooked - maybe because many dont know about them. Try to impress the "common people"

Best regards

Morten