Formula =COUNTIFS ... to not include certain text

Stacy Meadows

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

Stacy Meadows

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 😀

Genevieve P.

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

Stacy Meadows

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 😀

Genevieve P.

I'm glad you found a solution! 😊

Stacy Meadows

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

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

Genevieve P.

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

MeganMc

@Genevieve P. THANK YOU IT WORKED!