# Thread: SUMIFS Based on Two Conditions (2007)

1. ## SUMIFS Based on Two Conditions (2007)

Iā€™m trying to do a SUMIFS statement using Excel 2007 and Iā€™m have a difficult time getting the formula correct. Hereā€™s how my worksheet looks:
Column A is a date filed thatā€™s formatted mm/dd/yyyy
Column B is a test field that has names (e.g., Steve, Sam, Lucy, etc.)
Column C is a number filed
All the data input starts on row 4 and goes through row 40.
I would like my formula to look at column A and find all of the dates in January; then I would like the formula to look at column B and find all of the fields with Steveā€™s name, then provide me a sum of all Steveā€™s points in January.
Hereā€™s the formulas that I used:
=SUMIFS(C4:C20,A4:A20,ā€¯01/**/****ā€¯,B4:B20,ā€¯Steveā€¯)
The formula returns either returns a 0 value or a #VALUE! Error. I have attached a copy of the worksheet. Any assistance is greatly appreciated.

2. ## Re: SUMIFS Based on Two Conditions (2007)

I don't have 2007 so this is untested. It looks like the dates in A4:A20 are Excel serial dates, rather than the text that formula is testing against.
=SUMIFS(C4:C20,A4:A20,">="&DATEVALUE(ā€¯01/01/2008ā€¯),B4:B20,ā€¯Steveā€¯)
I don't know if
=SUMIFS(C4:C20,A4:A20,">="&DATEVALUE(ā€¯01/01/2008ā€¯),A4:A20,"<"&DATEVALUE(ā€¯02/01/2008ā€¯),B4:B20,ā€¯Steveā€¯) will be accepted, but
=SUMIFS(C4:C20,A4:A20,"<"&DATEVALUE(ā€¯02/01/2008ā€¯),B4:B20,ā€¯Steveā€¯) - SUMIFS(C4:C20,A4:A20,"<"&DATEVALUE(ā€¯01/01/2008ā€¯),B4:B20,ā€¯Steveā€¯) should work.

If you want January of any year,
=SUMPRODUCT(C4:C20,--(MONTH(A4:A20),--(B4:B20="Steve"))

3. ## Re: SUMIFS Based on Two Conditions (2007)

Shouldn't

=SUMPRODUCT(C4:C20,--(MONTH(A4:A20),--(B4:B20="Steve"))

be

=SUMPRODUCT(C4:C20,--(MONTH(A4:A20)=1),--(B4:B20="Steve"))

or

=SUMPRODUCT(C4:C20,(MONTH(A4:A20)=1)*(B4:B20="Stev e"))

4. ## Re: SUMIFS Based on Two Conditions (2007)

Mikerickson and Hans,
Thanks for your help. The SUMPRODUCT formual worked perfect!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•