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

 

[Aladin Akyurek]

Try:

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

 

[bozzy71]

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

 

[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?

Select the formula cell.
Activate Find & Select from the ribbon.
Activate Replace.
Activate the Find tab.
Set Find what to: =
Set Replace with to: =
Click on the Replace button.

 

[bozzy71]

Thanks - but it says there is an error in the formula? Thanks

 

[AliGW]

Try removing this (in red):


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

 

[bozzy71]

Thanks - getting there I think but I get the "#value!" I don't know of the below copy of the table works?

[TABLE="width: 697"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Column1[/TD]
[TD]12 Dec 16-18 Dec 16[/TD]
[TD]19 Dec 16 - 02 Jan 17[/TD]
[TD]11/1/17 - 30/4/17[/TD]
[TD]1/5/17 - 31/7/17[/TD]
[TD]15/10/17 - 30/11/17[/TD]
[/TR]
[TR]
[TD]Cobblers GS[/TD]
[TD="align: right"]$772.00[/TD]
[TD="align: right"]$892.00[/TD]
[TD="align: right"]$772.00[/TD]
[TD="align: right"]$476.00[/TD]
[TD="align: right"]$516.00[/TD]
[/TR]
[TR]
[TD]Ocean View Suite[/TD]
[TD="align: right"]$960.00[/TD]
[TD="align: right"]$1,060.00[/TD]
[TD="align: right"]$960.00[/TD]
[TD="align: right"]$508.00[/TD]
[TD="align: right"]$572.00[/TD]
[/TR]
[TR]
[TD]Ocean Front Suite[/TD]
[TD="align: right"]$1,124.00[/TD]
[TD="align: right"]$1,268.00[/TD]
[TD="align: right"]$1,124.00[/TD]
[TD="align: right"]$624.00[/TD]
[TD="align: right"]$696.00[/TD]
[/TR]
[TR]
[TD]2 Bedroom Suite 2-4 pax[/TD]
[TD="align: right"]$1,404.00[/TD]
[TD="align: right"]$1,548.00[/TD]
[TD="align: right"]$1,404.00[/TD]
[TD="align: right"]$904.00[/TD]
[TD="align: right"]$976.00[/TD]
[/TR]
[TR]
[TD]Cameot[/TD]
[TD="align: right"]$2,152.00[/TD]
[TD="align: right"]$2,152.00[/TD]
[TD="align: right"]$2,152.00[/TD]
[TD="align: right"]$1,116.00[/TD]
[TD="align: right"]$1,116.00[/TD]
[/TR]
[TR]
[TD]Colleton[/TD]
[TD="align: right"]$2,152.00[/TD]
[TD="align: right"]$2,152.00[/TD]
[TD="align: right"]$2,152.00[/TD]
[TD="align: right"]$1,116.00[/TD]
[TD="align: right"]$1,116.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[AliGW]

Try this:

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

The date types were different.

 

[63falcondude]

You have a "date range" in a single cell. I would recommend changing this so that you have the beginning date in one cell and the end date in another cell.

 

[bozzy71]

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"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><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]