Trying to use SUMIFS function to count spend in 2020 by Firm

Struggling newbie here!

For the life of me, I can not figure this out. I am trying to find the spend of each firm, and qualify it by the year so that once 2021 rolls around, I can track spend-by-year. I know the end of my formula below is wrong, but I cannot figure out how to qualify by formula by year. I've mastered the SUMIF function, but the SUMIFS is getting the best of me. The date column being reference is a MM/DD/YY format.

=SUMIFS({$$}, {firm}, "Lathrop", [{date}, "2020"}])

Thank you!


Best Answer


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    First you will need to remove the square brackets.

    Next you will need to reference the year like so...

    IFERROR(YEAR(@cell), 0) = 2020

    Which gives you...

    =SUMIFS({$$}, {firm}, "Lathrop", {date}, IFERROR(YEAR(@cell), 0) = 2020)

  • Sam Clapp
    Sam Clapp ✭✭✭

    That did it! Thank you!! Where would I go to get more educated on when the IFERROR function is necessary? I didn't even think to look into that function!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    HERE is the IFERROR function and how to use it. It is hard to say when that particular function is actually needed but some use cases would be very similar to your own.

    If you use a MONTH function


    pointing at a date type column, but there are some cells in that range that are blank or have a non-date value in them, then the MONTH function would throw an error.

    So if you wrap the MONTH function in the IFERROR function, it essentially replaces that error with (in this particular case) a zero. Since there is no month zero, it will not be included in any counts.

    I personally use it most when dealing with date based functions such as MONTH and YEAR, but there are tons of applications for it.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!