• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countif with < or > sign

Hi,
Anyone can help?
I face one issue regarding countif or sumif with < or > sign.
I suppose it is a string, but i don't know why my formula not work.
Please refer to below screen for detail.
70487
 
Since you suggest that you might like to know why your current formula fails:
The criterion "<=10" in
= COUNTIFS(score, criterion)
has a special meaning, namely return a count of numbers less than or equal to 10. The wildcard "*" in @Khalid NGO 's formula causes the count function to return the count of strings ending in "<=10".
Note: Searching for "*=10" will return ">=10" as well as "<=10" or "=10".
 
Since you suggest that you might like to know why your current formula fails:
The criterion "<=10" in
= COUNTIFS(score, criterion)
has a special meaning, namely return a count of numbers less than or equal to 10. The wildcard "*" in @Khalid NGO 's formula causes the count function to return the count of strings ending in "<=10".
Note: Searching for "*=10" will return ">=10" as well as "<=10" or "=10".
Thanks for clear explanation.....
 
Other array formulas that will work include
= COUNT(IF(score=@criterion,1))
and
= SUM(--(score=@criterion))
where @criterion is modern notation for a relative reference to the named range 'criterion'.
What one loses is the performance of COUNTIFS etc.
 
Back
Top