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

12/31/20
Accepted

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!

Popular Tags:

Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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)

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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 ClappSam 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 NewcomePaul 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

    MONTH(@cell)

    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.

Sign In or Register to comment.