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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!