Count if date is in the last six months

I'm trying to refine my metrics for the number of Packages archived in the last year. This formula (which is working correctly across two sheets)...

=COUNTIFS({Package Status 1}, CONTAINS("Archived", @cell), {Package Type 1}, "HWMP") + COUNTIFS({Package Status 2}, CONTAINS("Archived", @cell), {Package Type 2}, "HWMP")

...would be narrowed to only count the HWMP-type packages in sheet 1 with an Archived status in the last year plus the HWMP-type packages in sheet 2 with an Archived status in the last year. This formula returns #INVALID OPERATION

=COUNTIFS({Package Status 1}, CONTAINS("Archived", @cell), {Package Type 1}, "HWMP",TODAY() - {Final Archive Date 5} < 365) + COUNTIFS({Package Status 2}, CONTAINS("Archived", @cell), {Package Type 2}, "HWMP",TODAY() - {Archive Date} < 365)

Tags:

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Try:

    =COUNTIFS({Package Status 1}, CONTAINS("Archived", @cell), {Package Type 1}, "HWMP",{Final Archive Date 5}, @cell> today(-365)) + COUNTIFS({Package Status 2}, CONTAINS("Archived", @cell), {Package Type 2}, "HWMP", {Archive Date}, @cell > today(-365))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Try:

    =COUNTIFS({Package Status 1}, CONTAINS("Archived", @cell), {Package Type 1}, "HWMP",{Final Archive Date 5}, @cell> today(-365)) + COUNTIFS({Package Status 2}, CONTAINS("Archived", @cell), {Package Type 2}, "HWMP", {Archive Date}, @cell > today(-365))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    Hi Gabby,

    As I understand it, the COUNTIFS function looks for both a range (R) and criterion (C).

    So, the second formula above has:

    R1 of {Package Status 1}

    C1 of CONTAINS("ARCHIVED", @cell)

    R2 of {Package Type 1}

    C2 of "HWMP"

    R3 of TODAY() - {Final Archive Date 5} < 365 (which I'm assuming should be the criterion?)

    C3 of ?

    I'm wondering if it should read

    R3 of {Final Archive Date 5}

    C3 of ( TODAY() - {Final Archive Date 5} ) <365

    and same again for the second COUNTIFS function in the formula?

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Thanks, @Mark Cronk and @Jason Albrecht ! Adding {Archive Date}, @cell > today(-365) to the string for each sheet worked! For whatever reason, the two suggestions didn't provide the same result.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Glad you found a solution. Thank you for contributing to the community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!