Formula =COUNTIFS ... to not include certain text

ok almighty people.....

I currently have

=COUNTIFS({Depots1}, [Column1]23, {Product1}, FIND("Other", @cell) > 0)

This counts cells that depot is = column 1 row 23, if the product column contains the word Other (product column a multiple select cell, with free type option)

However I want to count cells that depot is = column 1 row 23, & Product column is not blank, & if product column does not contain data in primary column rows 24 - 47, this is a list of products...

I want it to basically count cells that have been free typed :)

Thank you

Tags:

Best Answer

  • Stacy Meadows
    Stacy Meadows ✭✭✭
    edited 04/22/22 Answer ✓

    Problem Solved with the assistance of @Genevieve P.

    here is how

    =COUNTIFS({Depots1}, [Column1]23, {Product1}, <>"", {Product1}, NOT(HAS(@cell,"Product Name")))

    The bold italic writing replaces column and row as it does not work this way ... So you need to "product name" and then it works

    =COUNTIFS({Depots1}, [Column1]23, {Product1}, <>"", {Product1}, NOT(HAS(@cell,"Hammer")), {Product1}, NOT(HAS(@cell,"Spanner")), {Product1}, NOT(HAS(@cell,"Axe")))

    and so on .... Works a treat ...

    Thank you 😀

Answers

  • Hi @Stacy Meadows

    I would suggest using the HAS Function instead of FIND if you're looking in a multi-select column. This will search to see if a cell has that specific value selected, like so:

    =COUNTIFS({Depots1}, [Column1]23, {Product1}, HAS(@cell, "Other"))


    Then if you're looking for if a cell does not have a specific value, you can use NOT(HAS. You can also use <> meaning "not equal to" to say <> "" for "not Blank".

    =COUNTIFS({Depots1}, [Column1]23, {Product1}, <> "", {Product1}, NOT(HAS(@cell, [Primary Column]24)))

    However there isn't a way to select a range (row 24 - 47) within the same HAS function. You will need to search for each individual possibility and exclude that value from your COUNTIFS:

    =COUNTIFS({Depots1}, [Column1]23, {Product1}, <> "", {Product1}, NOT(HAS(@cell, [Primary Column]24)), {Product1}, NOT(HAS(@cell, [Primary Column]25)), {Product1}, NOT(HAS(@cell, [Primary Column]26.... etc


    Let me know if that makes sense and works for you!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Stacy Meadows
    Stacy Meadows ✭✭✭
    edited 04/22/22 Answer ✓

    Problem Solved with the assistance of @Genevieve P.

    here is how

    =COUNTIFS({Depots1}, [Column1]23, {Product1}, <>"", {Product1}, NOT(HAS(@cell,"Product Name")))

    The bold italic writing replaces column and row as it does not work this way ... So you need to "product name" and then it works

    =COUNTIFS({Depots1}, [Column1]23, {Product1}, <>"", {Product1}, NOT(HAS(@cell,"Hammer")), {Product1}, NOT(HAS(@cell,"Spanner")), {Product1}, NOT(HAS(@cell,"Axe")))

    and so on .... Works a treat ...

    Thank you 😀

  • I'm glad you found a solution! 😊

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • @Genevieve P. I could not have done it with out your help, Thank you 🙏

  • MeganMc
    MeganMc ✭✭

    I am trying to use this logic and can't figure out why it isn't working for me

    =COUNTIF({CK-US: Add-on Requests Range 3}, AND(@cell >= DATE(2024, 1, 1), @cell <= DATE(2024, 1, 31)), {CK-US: Add-on Requests Range 4}, NOT(HAS(@cell, "Reset Requirement")))

  • Hi @MeganMc

    It looks like you're using the single version of COUNTIF, where you only have one criteria. Since you have more than one, you'll want to use COUNTIFS with an S.

    This is my personal preference for how to write your same formula:

    =COUNTIFS({CK-US: Add-on Requests Range 3}, @cell >= DATE(2024, 1, 1), {CK-US: Add-on Requests Range 3}, @cell <= DATE(2024, 1, 31), {CK-US: Add-on Requests Range 4}, NOT(HAS(@cell, "Reset Requirement")))


    If you're still getting an error, it would be helpful to see a screen capture of your source sheet, identifying the columns you're looking at, as well as what formula error you're seeing.

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!