Countif with < or > sign

[KIM Chanthan]

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.

 

[Khalid NGO]

Hi,

You need to enter wildcard like:

=COUNTIF($A$1:$A$10,"*"&D1)

Regards,

 

[Peter Bartholomew]

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".

 

[KIM Chanthan]

Hi,

You need to enter wildcard like:

=COUNTIF($A$1:$A$10,"*"&D1)

Regards,

Thanks a lot for help

 

[KIM Chanthan]

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.....

 

[Peter Bartholomew]

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.