SUMIFS or SUMPRODUCT with multiple criteria including date ranges

**baxcat** · 04-04-2013, 04:20 PM

I have been fumbling with this one for awhile and I think it is time to reach out!

I have three columns and I would like to total the HOURS column for each individual listed in the NAME column, based on a date range (ie. between Apr 1 to Apr 6).

NAME HRS DATE
Bob 2 2-Apr-13
Bob 1 4-Apr-13
Sam 2 2-Apr-13
Bob 4 8-Apr-13

I have tried SUMIFS and SUMPRODUCT, but I'm falling short. The date range is particularly challenging. Is this doable?

Thanks!

**djapigo** · 04-04-2013, 04:26 PM

Try this...

**Harribone** · 04-04-2013, 04:31 PM

Assumin:

Col A is NAME
Col B is HOURS
Col C is DATE

F2 enter Name of choice
F3 enter start date
F4 enter end date

F5 type - =SUMIFS(B:B,A:A,F1,C:C,">="&F2,C:C,"<="&F3)

Don't know if SUMIFS works in 2003 so may need sumproduct solution (don't do these myself - never learnt how to use!)

EDIT: Beaten to it! Above solution from djapigo will do the job.

**djapigo** · 04-04-2013, 04:35 PM

Harribone,

Your formula will work for Excel 2007 or higher, but I figured baxcat only had Excel 2003... the SUMIFS formulas are pretty new and nice formulas...

**baxcat** · 04-25-2013, 09:40 AM

Thank you Harribone & djapigo. I neglected to change my stats to show that we have updated to Excel 2010 at work. Both solutions worked perfectly. Thank you very much. I can apply this in many ways.