Calculate TY/LY comp by aggregate daily sales

[jmuffintop]

I am trying to calculate a live sales comp for 2017/2018.

In order to do the basic comp calculation, I need to sum 2017 sales up to the last corresponding sales input line for 2018. In this example it would be 2000. If I entered sales for 6/4/2018, the new aggregate sum for 2017 would be 2500. Any idea how to accomplish this?


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Day[/TD]
[TD]Date[/TD]
[TD]Sales[/TD]
[TD][/TD]
[TD]Day[/TD]
[TD]Date[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Thurs
[/TD]
[TD]6/1/2017[/TD]
[TD]500[/TD]
[TD][/TD]
[TD]Thurs[/TD]
[TD]5/31/2018[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]Fri[/TD]
[TD]6/2/2017[/TD]
[TD]500[/TD]
[TD][/TD]
[TD]Fri[/TD]
[TD]6/1/2018[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]Sat[/TD]
[TD]6/3/2017[/TD]
[TD]500[/TD]
[TD][/TD]
[TD]Sat[/TD]
[TD]6/2/2018[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]Sun[/TD]
[TD]6/4/2017[/TD]
[TD]500[/TD]
[TD][/TD]
[TD]Sun[/TD]
[TD]6/3/2018[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]Mon[/TD]
[TD]6/5/2017[/TD]
[TD]500[/TD]
[TD][/TD]
[TD]Mon[/TD]
[TD]6/4/2018[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Tues[/TD]
[TD]6/6/2017[/TD]
[TD]500[/TD]
[TD][/TD]
[TD]Tues[/TD]
[TD]6/5/2018[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Weds[/TD]
[TD]6/7/2017[/TD]
[TD]500[/TD]
[TD][/TD]
[TD]Weds[/TD]
[TD]6/6/2018[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Thurs[/TD]
[TD]6/8/2017[/TD]
[TD]500[/TD]
[TD][/TD]
[TD]Thurs[/TD]
[TD]6/7/2018[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Fri[/TD]
[TD]6/9/2017[/TD]
[TD]500[/TD]
[TD][/TD]
[TD]Fri[/TD]
[TD]6/8/2018[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]
-

 

[Chameleon64]

Assuming your table starts in A1. Enter a helper field in cell I1 which determines the earliest date in the 2018 table which doesn't have an associated value:

I1: {=MIN(IF(G2:G10=0,F2:F10))} This is an array formula so use CTRL-SHIFT-ENTER when entering the formula

The formula for the aggregate sum is then:

=SUMIF(B2:B10,"<="&EDATE(I1,-12),C2:C10)