How to find specific text within a string, in a COUNTIFS statement

(This was posted as a question previously, however I am unable to mark that as "unanswered" and am asking this again)

Hi everyone,

I am using a COUNTIFS to search a linked sheet, with one of the conditional IFS to be if the date falls in a specific month, and a specific year.

Example: Condition 1, Condition 2, Condition 3 = Must have "Jan" in referenced dates, Condition 4 = Year must be "2022" in referenced dates

Have tried "*"&"jan"&"*" as both an IF, and as part of a nested FIND statement - both to no success.

A previous posts incorrect answer proposed this:

COUNTIFS(

{Studio Follow-On Projects Status (Prod) Range 1}, Jurisdiction@row,

{Studio Follow-On Projects Status (Prod) Range 2}, <>"DONE",

{Studio Follow-On Projects Status (Prod) Range 2}, <>"CANCELLED",

{Studio Follow-On Projects Status (Prod) Range 4},

IFERROR(MONTH(@cell),0)=4)

Any other tips please?

Find more posts tagged with

Accepted answers

All comments

Mark Cronk

Hi Rudy,

Please share a screenshot of your sheet with column headings.

The basic structure of your formula for the month of January and year 2021 will be:

AND({Condition 1}, IFERROR(MONTH(@cell),0)=1, {Condition 2}, IFERROR(MONTH(@cell),0)=1, {Condition 3}, IFERROR(MONTH(@cell),0)=1, {Condition 4}, IFERROR(year(@cell),0)="2021")

Your condition ranges need to point to single date columns.

Mark

Rudy Nausch

Hi Mark,

Thank you for your input.

Can I share the sheets with you to take a look?

Cheers,

Rudy

Rudy Nausch

I am attempting to count the number of titles by Release and by Date 3, from the sheet below (Test Project Status).

The query is to count these into the sheet below, based on those conditions.

Not sure how to make the IFERROR statement work in this context.

Paul Newcome

Are you entering the dates into a date type column or are they in a text/number column?

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.