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
Best 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
-
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
-
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 🙏
-
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
-
@Genevieve P. THANK YOU IT WORKED!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 458 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!