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?

Answers

  • Bassam Khalil
    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.

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Andrée Starå
    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, [email protected])

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hi Bassam,

    Sure -

    =COUNTIFS(

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

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

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

  • Rudy Nausch
    edited 06/30/21

    Alternate is:

    =COUNTIFS(

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

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

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

  • Andrée Starå
    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?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Rudy Nausch
    edited 06/30/21

    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å
    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?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Rudy Nausch
    edited 06/30/21

    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å
    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, [email protected])

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hi Andree - have sent you the sheet

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Rudy Nausch

    Excellent!

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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Rudy Nausch
    edited 07/06/21

    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

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭

    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.

  • 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?

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    edited 07/07/21

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!