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 2821 users have said thank you to us this month