Trying to add a year to this formula

Drew Wort

So here is my current formula…

=COUNTIFS({Date Completed}, IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()), {Staff name}, =Staff@row )

This works. It gives me a number for all observations submitted in the month.

The problem is it also counts the month for the previous year. How do i filter the count by month and year?

Thank You!

Find more posts tagged with

Formulas

Accepted answers

Jgorsich

You have 2 criteria, just add a third:

=COUNTIFS({Date Completed}, IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()), {Staff name}, =Staff@row,{Date Completed}, IFERROR(year(@cell ), 0) = year(TODAY()))

Jgorsich

If you wanted to make it even more compact, you can next "and()" and "or()" functions in it as well - this, for example, should also work (though I've not tested it):

=COUNTIFS({Date Completed}, and(IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()),IFERROR(year(@cell ), 0) = year(TODAY())), {Staff name}, =Staff@row )

But I personally find that a lot harder to read and to maintain over time vs spreading them out and really only do it when the situation REALLY benefits from it.

All comments

Jgorsich

You have 2 criteria, just add a third:

=COUNTIFS({Date Completed}, IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()), {Staff name}, =Staff@row,{Date Completed}, IFERROR(year(@cell ), 0) = year(TODAY()))

Drew Wort

Oh my. I was trying to add it in the middle and must have screwed something up. THANK YOU

Jgorsich

If you wanted to make it even more compact, you can next "and()" and "or()" functions in it as well - this, for example, should also work (though I've not tested it):

=COUNTIFS({Date Completed}, and(IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()),IFERROR(year(@cell ), 0) = year(TODAY())), {Staff name}, =Staff@row )

But I personally find that a lot harder to read and to maintain over time vs spreading them out and really only do it when the situation REALLY benefits from 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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.