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?

Day	Date	Sales		Day	Date	Sales
Thurs	6/1/2017	500		Thurs	5/31/2018	700
Fri	6/2/2017	500		Fri	6/1/2018	700
Sat	6/3/2017	500		Sat	6/2/2018	700
Sun	6/4/2017	500		Sun	6/3/2018	700
Mon	6/5/2017	500		Mon	6/4/2018	-
Tues	6/6/2017	500		Tues	6/5/2018	-
Weds	6/7/2017	500		Weds	6/6/2018	-
Thurs	6/8/2017	500		Thurs	6/7/2018	-
Fri	6/9/2017	500		Fri	6/8/2018	-

<tbody>
</tbody>

-

 

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