COUNTIFS formula always return O as a value

JT Girard

I have use several times the COUNTIFS formula and it was working fine with dropdown list using text, seems to be a issue when the dropdown list use symbols like (<) or (>).

To give you context, i have a datebase where the user can choose the following cost impact.

dropdown list

<  2 500$

< 10 000$

< 25 000$

≥ 25 000$

i'm already able to return the number of accident per month using the countifs formula, but when a want to breakdown the frequency of the different cost impact, my countif formula doesn't recognise the <  2 500$. It gave me 0.

i've tried removing the (<) and it works but it would prefer to keep using those symbol to indicate under (<) or over (>_ a certain amount of money.

Here's my formula, is there any quick fix :

=COUNTIFS({Registre des Non-Conformités Range 7}, [Column10]@row, {Registre des Non-Conformités Range 9}, $[Column14]$2, {Registre des Non-Conformités Range 10}, $[Column15]$1)

Carson Penticuff

Try wrapping each of these in a HAS condition.

=COUNTIFS({Registre des Non-Conformités Range 7}, HAS(@cell, [Column10]@row), {Registre des Non-Conformités Range 9}, HAS(@cell, $[Column14]$2), {Registre des Non-Conformités Range 10}, HAS(@cell, $[Column15]$1))

Here is a recent discussion that, although not directly related to COUNTIFS, does mention the less than/greater than symbol issue when matching within a condition.

https://community.smartsheet.com/discussion/119406/if-formula-returns-unexpected-true-result-for-text-cells-that-start-with-the-or-character