COUNTIF for multiple data types

[investntrade]

Hi,

I have data in excel which contains alphanumeric values like "NA", blanks and spaces, as well as numbers as percentage:
For example, I have the 17 values in a column which includes 3 percentage numbers, 11 NA and rest blank values:

[TABLE="width: 69"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2.37%[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]NA[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]NA[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]1.37%[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[/TR]
[TR]
[TD="align: right"]-0.49%[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[/TR]
[TR]
[TD]NA


[/TD]
[/TR]
</tbody>[/TABLE]


I want to get the count of only those cells whose values are less than a specific range. In the above example, say I want to count values which are greater than 1%. So I put the 1% value in cell B2, and then use the following formula:
=COUNTIF(K2:K88,">-$B$2")

Unfortunately, the above is returning all counts of NA (i.e. 11), as it seems that NA is being considered as greater than the number 1%.

Expected output should be the count of 2 (for value of 2.37% and 1.37%) in the whole range.

Can the experts please help in getting the right formula?

Thanks

 

[Marcelo Branco]

Try

=COUNTIF(K2:K88,">"&$B$2)

where B2 = 1%

M.

 

[investntrade]

Try

=COUNTIF(K2:K88,">"&$B$2)

where B2 = 1%

M.

Thanks Marcelo,
Works perfectly!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.