Day on day comparison

[VbaHell]

Hello all

I hope you can help on this formula, I want to try to compare day on day sales comparison over 2018 / 2019

At the moment I have a sumifs statement that just adds up for each month and a running total for the year, it's fine for this year as it will add up to today but last year (2018) will add up the whole month and screw the full year comparison figures.

This is the formula that I use now which will add up the each month


[=SUMIFS(Sales!$I:$I,Sales!$X:$X,$B7,Sales!$E:$E,D$6,Sales!$U:$U,DashBoard!$C$4)]

I:I is the sales value
X:X is the month in Text format (Jan) or (Feb) Etc
E:E is the year in Text format (2018) or (2019)
D6 is the year (2018)
U:U is Sales people
C4 is the sales person
I also have a column with the date G:G [TABLE="width: 89"]
<colgroup><col></colgroup><tbody>[TR]
[TD]2018-01-02 which I suspect will need to be included[/TD]
[/TR]
</tbody>[/TABLE]


How can I modify this please so it only adds up to this day last year

 

[Rijnsent]

Hi VbaHell,

the formula for this day last year is: =date(year(today())-1,month(today()),day(today()) (simplified: =TODAY()-365 , but that doesn't take leap years into account).

So add another condition to your SUMIFS with column G and as a condition: "<=" & date(year(today())-1,month(today()),day(today())

See this example for some inspiration: https://basicexceltutorial.com/formula/excel-formula-to-sumif-between-two-dates

Cheers,

Koen

 

[VbaHell]

Hi Koen

Thank you for your reply

Do you mean something like this please

[=SUMIFS(Sales!$I:$I,Sales!$X:$X,$B7,Sales!$E:$E,$D$6,Sales!$U:$U,$C$4,Sales!$G:$G,"<=" & date(year(today())-1,month(today()),day(today)))

 

[Rijnsent]

Hi VbaHell,
looks like it should work, did you try it?
Koen