sumifs with date range

[bozzy71]

Hi,

I have a table with date ranges (UK format) in cells f5:h5 (i.e. f5 is 3/1/17-30/4/17, g5 is 1/5/17-31/7/17, h5 is15/10/17-30/11/17). I have associated costs in cells f6:h6. I have an offer that is only valid 11/3/17-31/10/17 which gives 20% off the prices f6:h6. how can i get the calculation to only look between these dates, sum the figures and apply the 20% discount?

I hope this makes sense!

Thanks
James

 

[bozzy71]

Hi nay idea how I can get it to sum the relevant prices? Thanks

 

[Aladin Akyurek]

Thanks for the prompt reply. When I pasted the above in it only shows the formula - it doesn't attempt to produce the result?

Missing an &...

=SUMIFS(F6:H6,F5:G5,">=11/3/17",F5:G5,"<="&31/10/17")*20%

 

[bozzy71]

thanks, but still returning zero?

 

[Aladin Akyurek]

What are the date criteria and the expected value?

Thanks. Doing this has produced an answer of zero. Obviously I can't seem to get it to sum the relevant costs and apply the 20% discount? Table now looks like this:

[TABLE="width: 1253"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]12/12/2016[/TD]
[TD]18/12/2016[/TD]
[TD]19/12/16[/TD]
[TD]02/01/17[/TD]
[TD]11/01/17[/TD]
[TD]30/04/2017[/TD]
[TD]01/05/2017[/TD]
[TD]31/07/2017[/TD]
[TD]15/10/2017[/TD]
[TD]30/11/2017[/TD]
[/TR]
[TR]
[TD]Cobblers GS[/TD]
[TD="align: right"]$772.00[/TD]
[TD="align: right"]$772.00[/TD]
[TD="align: right"]$892.00[/TD]
[TD="align: right"]$892.00[/TD]
[TD="align: right"]$772.00[/TD]
[TD="align: right"]$772.00[/TD]
[TD="align: right"]$476.00[/TD]
[TD="align: right"]$476.00[/TD]
[TD="align: right"]$516.00[/TD]
[TD="align: right"]$516.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[bozzy71]

where the dates when the 20% discount are applicable I would like it to sum the the amounts that are within the date bands. i.e the offer is between 11/3/17 and 31/oct/17. Therefore I'd like it to sum each amount reflected in the price bands below the relevant dates. I'm not sure if splitting the date ranges to separate cells is helping at all? I would expect the range 11/01/17-30/04/2017 to reflect one amount of $772, the same for 1/5/17-31/07/17 one amount of $476 etc. The total before discount would be $1764-20%=1411.20. Not sure if I'm over-complicating this? I have seen some sort of example (which i can't get my head around!) which may work? thanks


[FONT=&quot]

[/FONT]
[FONT=&quot]=SUMIFS(amount,start_date,">"&A1,end_date,"<"&B1)


[/FONT]

 

[Aladin Akyurek]

You should post your last exhibit in an Excel readable form...

 

[Dave Patton]

Excel 2010
	C	D	E	F	G	H	I
4
5	11-Mar-17		31-Jan-17	30-Apr-17	31-Jul-17	31-Oct-17	31-Jan-18
6	31-Oct-17		900	1,000	1,200	1,400	1,500
7	3,600
8	720	Discount
9		or
10	720	Discount
11
12
13	1-Sep-15	1,500				15-Sep-15
14	6-Sep-15	6,500				15-Oct-15
15	21-Sep-15	1,250				18,250
16	22-Sep-15	12,000
17	6-Oct-15	5,000
18	15-Oct-15	5,000
19	29-Oct-15	1,000
4b

Cell Formulas
Range	Formula
C7	=SUMIFS(E6:I6,E5:I5,"<="&C6,E5:I5,">"&C5)
C8	=C7*0.2
C10	=SUMIFS(E6:I6,E5:I5,"<="&DATE(2017,10,31),E5:I5,">"&DATE(2017,3,11))*0.2+N("Discount is .2 (20%)")
H15	=SUMIFS(D13:D19,C13:C19,"<"&H14,C13:C19,">"&H13)

I posted this in case the question was not resolved.
I edited some of the information to provide the example.

 

[bozzy71]

Thanks for this. Think nearly there but it is only returning a result of zero rather than the 3600 in C7?

 

[bozzy71]

Apologies, my error in the copying. Thanks for this - I think I'm starting to understand (eventually) how the formula works. Thank you all for your assistance and patience!