COUNTIFS for specific years

Hi, Im working on a countifs formula i which I would like to get the sum values for both 2024 and 2025. Below is my original formula for 2025… and now, I would like the same formula to also capture 2024.. Thanks in advance for the help! 😊

Find more posts tagged with

Accepted answers

Jeremy_D

Hi @regina.quality I think to do that you'll have to add the formula you have to the same formula but with 2024. So if your formula works then it'll be:

=COUNTIFS({MMR EQSW Function/Project Date Requested}, ISDATE(@cell ), {MMR EQSW Function/Project Date Requested}, YEAR(@cell ) = 2025, {MMR EQSW Function/Project Code}, "EP", {MMR Engineering Function/Project Status}, "Complete") + COUNTIFS({MMR EQSW Function/Project Date Requested}, ISDATE(@cell ), {MMR EQSW Function/Project Date Requested}, YEAR(@cell ) = 2024, {MMR EQSW Function/Project Code}, "EP", {MMR Engineering Function/Project Status}, "Complete")

I know it's quite wordy, there may be a better way but this will work.

regina.quality

@Jeremy_D Hi Jeremy, sorry for the late feedback. Much appreciate your advise/suggestion. And yes, it did work!

All comments

Jeremy_D

Hi @regina.quality I think to do that you'll have to add the formula you have to the same formula but with 2024. So if your formula works then it'll be:

I know it's quite wordy, there may be a better way but this will work.

regina.quality

@Jeremy_D Hi Jeremy, sorry for the late feedback. Much appreciate your advise/suggestion. And yes, it did work!

Paul Newcome

You can also shorten this a bit by using an OR function and using an IFERROR for the YEAR function to remove the need for the ISDATE piece.

=COUNTIFS({Date Requested}, OR(IFERROR(YEAR(@cell), 0) = 2024, IFERROR(YEAR(@cell), 0) = 2025), {Project Code}, "EP", {Project Status}, "Complete")

Note: I shortened the {Cross Sheet Reference} names so that the overall syntax was a little easier to read. If you update each of those to the appropriate names, the above should output the same results with a little more efficiency.

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.