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 spendbyyear. 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

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

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)

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!

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 nondate 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
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!