×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SUMIF and multiple criteria

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.

RE: SUMIF and multiple criteria

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.

RE: SUMIF and multiple criteria

An interesting alternative from the "help"

Quote:

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.

RE: SUMIF and multiple criteria

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

RE: SUMIF and multiple criteria

Two alternatives without a "helper" column:

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

(OP)
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.

RE: SUMIF and multiple criteria

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
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

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
 

RE: SUMIF and multiple criteria

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.
 

RE: SUMIF and multiple criteria

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" smile

Best regards

Morten

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources