Find the sum of data that has multiple conditions in Excel [Solved/Closed]

Report
Posts
5
Registration date
Thursday June 6, 2013
Status
Member
Last seen
June 13, 2013
-
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
-
Hello,

I have a large data table, and I want to find the sum for certain data that fits a few criteria.
Here is a simplified example:
__a_____b_____c____
1 Type Dates Amount (headings)
2 shirt 1/4/2013 $10
3 pants 6/8/2013 $20
4 shirt 3/9/2013 $15

Is there a compound formula or macro that could find the sum of money if it is a "shirt" and is within the first 3 months of the year?

The answer should equal $45 (10+15)

I would appreciate this soooooo much!!!!

1 reply

Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
9
Hi acohen12

Here are a couple of ways, the criteria is hard coded in, it would be better to reference cells for your criteria.

For Excel 2007 and later:
=SUMIFS(C2:C100,A2:A100,"Shirt",B2:B100,">=01/01/2013",B2:B100,"<=31/03/2013")

For Excel 2003 and earlier:
=SUMPRODUCT(--(A2:A100="Shirt"),--(B2:B100>="01/01/2013"+0),--(B2:B100<="31/03/2013"+0),C2:C100)

The answer should be $25 not $45!
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!