SUM of the past 3 years

Abbie G

=SUMIFS({Contract Value}, {Exp.Date}, AND(@cell >= DATE(YEAR(TODAY()) - 2, MONTH(TODAY()), DAY(TODAY())), @cell <= TODAY()), {Area}, "South")

This is supposed to COUNT the sum of the past 2 years, but when I changed it from 2 to 3 to find 3 years, it remained the same. How do I change this so it counts the past 3 years?

Also, how do it so that it counts the current year?

Bassam Khalil

Hi @Abbie G

Hope you are fine, the formula to define the date for the past 3 years is i check it and it's work:

=DATE(YEAR(TODAY()) - 3, MONTH(TODAY()), DAY(TODAY()))

try the following formula please:

=SUMIFS({Contract Value}, {Exp.Date}, AND(@cell >= (DATE(YEAR(TODAY()) - 3, MONTH(TODAY()), DAY(TODAY())), DAY(TODAY())), @cell <= TODAY()), {Area}, "South")

Abbie G

Thank you @Bassam Khalil ! Unfortunately, it looks like it coming up as unparesable

Paul Newcome

Your current formula should work with just changing the 2 to a 3.

=SUMIFS({Contract Value}, {Exp.Date}, AND(@cell >= DATE(YEAR(TODAY()) - 3, MONTH(TODAY()), DAY(TODAY())), @cell <= TODAY()), {Area}, "South")

Did you double check that there is data present in the source sheet that meets the criteria?