COUNTIF for a number within a Range

Simon Wilcock

Morning

I'm using the following formula to count how many times a specific value is found in a column where many different clause values are listed, sometimes with multiple values in each cell.

=COUNTIFS({Audit - Range 1}, FIND(Clause@row, @cell) > 0)

Example

4.1

4.1.2

4.1

4.4, 4.8, 6.7

For example, I want to count how many times the value 4.1 appears in a column (see above). This formula works but also counts any other cell with the same value in a string as shown above. It count 4.1 3 times because its also in the 4.1.2 and I want to ONLY count the 4.1 values so return 2 times.

I'm hoping for some sort of unique value ability.

Any ideas?

Thanks

Simon Wilcock

Perfect, I will try that. Thanks for the help!

Genevieve P.

Hi @Simon Wilcock

Is the column a Multi-Select column?

If so, you can use the HAS function instead of Find to see if the cell HAS a specific value selected. For example:

=COUNTIFS({Audit - Range 1}, HAS(@cell, Clause@row))

Let me know if this works for you!

Cheers,

Genevieve

Simon Wilcock

Perfect, I will try that. Thanks for the help!

Simon Wilcock

FYI, this worked perfectly, thanks 😀