Sumifs with date range - help please!

**RuthieRu** · 08-14-2012, 04:39 AM

Hi all,

I'm new to this forum business, apologies if I don't do it quite right! I'm not very advanced in excel and have hit a brick wall...

I am trying to write a sumifs formula that will sum total stock delivered within a date range. I've attached the file complete with formula error. I'm trying to build a stock taking spreadsheet that will allow the user to specify the range of weeks he has between stock takes and then sum up the total delivered and the total sales and use these to calculate an expected stock.

I have searched lots of excel forums etc but still cannot get this to work. My formula is: =sumifs(D6:D21,C6:C21,">="&H4,C6:C21,"<="&I4) where D6:D21 is the data I want summed, C6:C21 my criteria range and H4 and I4 are my dates (they work on drop down list selection).

If you are able to help with this that would be amazing.

Thanks,

Ruth

**Ace_XL** · 08-14-2012, 05:08 AM

The essence of your formula is correct..

I modified it to
=SUMIFS(C4:F4,$C$3:$F$3,">="&$H$3,$C$3:$F$3,"<="&$I$3)

See attached highlighted in yellow. Is this something you can work off?

**RuthieRu** · 08-14-2012, 05:22 AM

Hi Ace_Xl,

Many thanks for such a quick reply - when I open the file, the formula doesn't work if I change the date? E.g. change 18/08/12 for 25/08/12?

Also, what was I doing wrong?

Thanks,

Ruth

**daddylonglegs** · 08-14-2012, 05:31 AM

Are you using Excel 2003? SUMIFS doesn't work in that version, try subtracting one SUMIF from another, i.e.

=SUMIF($C$3:$F$3,">="&$H$3,C4:F4)-SUMIF($C$3:$F$3,">"&$I$3,C4:F4)

**RuthieRu** · 08-14-2012, 05:50 AM

hi daddylonglegs,

Thanks - I didn't realise this. I am doing a bit of both (work vs home laptop) but now appreciate that isn't going to work. Thinking about it end users of the sheet may only have 2003 so I'll do this - thanks :-)

Ruth