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

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.

So Condition 1, Condition 2, Condition 3 = Must have "Jan" in referenced dates

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

Any tips please?

Find more posts tagged with

Accepted answers

All comments

Bassam Khalil

Hi @Rudy Nausch

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Andrée Starå

Hi @Rudy Nausch

I hope you're well and safe!

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Rudy Nausch

Hi Bassam,

Sure -

=COUNTIFS(

{ Status_Sheet Range 1}, $Column One@row,

{Status_Sheet Range 2}, <>"DONE",

FIND("Jan", {Status_Sheet Range 4}))

Rudy Nausch

Alternate is:

=COUNTIFS(

{ Status_Sheet Range 1}, $Column One@row,

{Status_Sheet Range 2}, <>"DONE",

{Status_Sheet Range 4},"*"&"Jan"&"*"))

Andrée Starå

@Rudy Nausch

Try something like this.

=COUNTIFS({Status_Sheet Range 1},[Column One]@row,{Status_Sheet Range 2}, <>"DONE", {Status_Sheet Range 4}, IFERROR(MONTH(@cell), 0) = 4)

Did that work?

Rudy Nausch

Hi Andree - appreciate the speedy responses!

That does not seem to work, maybe i am incorrectly using the expression :

=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",

IFERROR(MONTH(@{Studio Follow-On Projects Status (Prod) Range 4},0)

My goal is to count all the dates in a given month, say "january" from the date format **-jan-021

Andrée Starå

@Rudy Nausch

Happy to help!

Try 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)

Did that work?

Rudy Nausch

I get a vlaue of zero - am i misunderstanding the expression maybe?

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

Should that be:

IFERROR("January"(@cell), 0) = 4

Does MONTH = the month i am looking to count i.e. "January"

And does (@cell) need to refer to the date column in the referenced sheet?

Andrée Starå

@Rudy Nausch

No, that should work, and it probably does, and it shows 0 because there is an error.

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Rudy Nausch

Hi Andree - have sent you the sheet

Andrée Starå

@Rudy Nausch

Excellent!

I'll take a look and get back to you.

Rudy Nausch

Hi all,

Will be posing this question again as I do not know how to mark this as unanswered

Received no follow up after the above post

Thank you

TVang

Hmm...if you're trying to count "January" items, why are you setting it "=4"? That's April. January is "1".

Your last criterion should be IFERROR(MONTH(@cell),0)=1 if you want the cell counted when the month of whatever date is in the cell in Range 4 is "January".

= 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) = 1)

Sorry, if I jumped into the middle of it all and may have missed an important detail.

Rudy Nausch

Hi Toufong,

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.

Any thoughts?

TVang

Hi, Rudy.

Off the bat, the problem is that--while your [Month] column looks like a date type to you and me--it is not one that is recognized by Smartsheet (see screengrab below). My guess is that "Jan-21" and the values in [Month] are strings and not a Smartsheet date datatype.

In order for it to be evaluated as a date, whatever is in [Month] needs to be converted into a date. Otherwise, your formula will need to be "customized" for each [Month] and for each [Release] type. For example...

NSW_Counts for "Jan-21" (=1) would be...

= COUNTIFS({Range1_is_Release_Column}, @cell="NWS", {Range2_is_Date_3_Column}, AND(IFERROR(YEAR(@cell),0)=2021, IFERROR(MONTH(@cell),0)=1))

QLD_Counts for "Jan-21" would be...

= COUNTIFS({Range1_is_Release_Column}, @cell="QLD", {Range2_is_Date_3_Column}, AND(IFERROR(YEAR(@cell),0)=2021, IFERROR(MONTH(@cell),0)=1))

VIC_Counts for "Jan-21" would be...

= COUNTIFS({Range1_is_Release_Column}, @cell="VIC", {Range2_is_Date_3_Column}, AND(IFERROR(YEAR(@cell),0)=2021, IFERROR(MONTH(@cell),0)=1))

and so on...

You can avoid having to do this for each month by using a "helper" column for the month. Let's say that you have a [Month Helper] column where "202101" is the corresponding value for "Jan-21" and so on where "202202" is for "Feb-22".

Then your formula for the NSW column for "Jan-21" would be...

= COUNTIFS({Range1_is_Release_Column}, @cell="NWS", {Range2_is_Date_3_Column}, AND( IFERROR(YEAR(@cell),0)=VALUE(LEFT([Month Helper]@row,4)), IFERROR( MONTH(@cell),0) = VALUE(RIGHT([Month Helper]@row,2))))

And your formula for the next column, QLD, would be...

= COUNTIFS({Range1_is_Release_Column}, @cell="QLD", {Range2_is_Date_3_Column}, AND( IFERROR(YEAR(@cell),0)=VALUE(LEFT([Month Helper]@row,4)), IFERROR( MONTH(@cell),0) = VALUE(RIGHT([Month Helper]@row,2))))

...and so on.

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.