Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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

Answer 1 / 1
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
Thank you

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

CCM 2942 users have said thank you to us this month

Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!