CountIF one of multiple criteria are met within the current year.

Good Afternoon. I'm trying to get a count if a specific event is documented to occur within any row in specific columns. This is where I'm at, although unsuccessfully....

=COUNTIFS({Range 1}, "Value1", {Range 2}, "Value 1", {Date Range 1}, AND(IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))))

I need the sheet to count if "Value 1" appears in either "Range 1" or "Range 2" columns. Not that it must appear in both. I also need it to count of the date the value was generated was within the current year. I know the IFERROR function works, I just don't think I have the whole thing set up correctly. Thanks in advance!

Find more posts tagged with

Accepted answers

Kris V. Le

https://community.smartsheet.com/discussion/comment/308057#Comment_308057

Kelly, thank you very much! That appears to have solved the issue.

All comments

Kimberly Loveless

Try this one...

= COUNTIFS(OR({Range 1} = "Value 1", {Range 2} = "Value 1"), {Date Range 1},IFERRIOR(YEAR(@cell),0)=YEAR(TODAY()))

Now one thing is this is what will work if Range 1, Range 2, and Date Range 1 are cross sheet references if not then those references in the formula will be slightly different

Kris V. Le

I'm getting an "Invalid Operation" Error on this format.

Kelly Moore

Hey @Kris V. Le

Because the 'OR' is occurring between multiple ranges and not choices within a single range you need to add the COUNTIFS together.

= COUNTIFS({Range 1},"Value 1", {Date Range 1},IFERROR(YEAR(@cell),0)=YEAR(TODAY())) + COUNTIFS({Range 2},"Value 1", {Date Range 1},IFERROR(YEAR(@cell),0)=YEAR(TODAY()))

Does this work for you?

Kris V. Le

https://community.smartsheet.com/discussion/comment/308057#Comment_308057

Kelly, thank you very much! That appears to have solved the issue.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.