COUNTIFS formula always return O as a value
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)
Answers
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!