SUM of the past 3 years

Options
Abbie G
Abbie G ✭✭
edited 06/15/21 in Formulas and Functions

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

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Abbie G
    Abbie G ✭✭
    Options

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!