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!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

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)

All comments

Paul Newcome

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

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

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 to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.