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