Countifs Formula where one value may be a fragment or a word (but I only want it counted as word)

Options

Hello,

I've written a countifs formula for the number of times certain words show up in a column on another sheet based on a date column in that sheet. The formula works fine for most words such as "Recruitment" or "Compliance". However, I have one value that is "NCE" and that's where I'm having the problem.

I need to include in the overall formula a way to count only those instances of "NCE" as a stand alone word, not when it shows up as part of another word such as "CompliaNCE". That's what the formula is currently doing.

If it helps, here's my formula, which again, works fine for everything else except "NCE" as a stand alone:

=COUNTIFS({Red Flag Report Items Range 1}, CONTAINS([Primary Column]@row, @cell), {Red Flag Report Items Range 2}, Period12)

"Red Flag Report Items" are the sheet that contains the word and date values I'm evaluating, respectively. [Primary Column]@row is the string I'm searching for.

I hope that helps and look forward to solutions, thanks!

Russ

Tags:

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 06/04/21 Answer ✓
    Options

    Hi Russ,

    Try using HAS. It returns true if there is an exact match. Words like NCE will need to be the only word in the cell or one of a multi-select drop down.

    =COUNTIFS({Red Flag Report Items Range 1}, HAS(@cell, [Primary Column]@row), {Red Flag Report Items Range 2}, Period12)

    https://help.smartsheet.com/function/has

    Work for you?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 06/04/21 Answer ✓
    Options

    Hi Russ,

    Try using HAS. It returns true if there is an exact match. Words like NCE will need to be the only word in the cell or one of a multi-select drop down.

    =COUNTIFS({Red Flag Report Items Range 1}, HAS(@cell, [Primary Column]@row), {Red Flag Report Items Range 2}, Period12)

    https://help.smartsheet.com/function/has

    Work for you?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Works like a charm, thanks Mark! I had played around with HAS previously but couldn't get it to work. What you state is true, NCE is part of a multi-select drop down.

    I was able to add a third variable to screen out those cells that also include "No Issue". Final formula below for reference.

    =COUNTIFS({Red Flag Report Items Range 1}, HAS(@cell, [Primary Column]@row), {Red Flag Report Items Range 2}, Period15, {Red Flag Report Items Range 1}, NOT(CONTAINS("No Issue", @cell)))

    I appreciate the support!

    Russ

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi Russ, Excellent. Glad you found a solution. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!