Count numbers and non numbers(conditional values)

[Ecoman]

I can't seem to make a formula work that will count numbers greater than 3.7
as well as cells that contain the value <5 as below.

2 4 3.8 7 <5 1 2.3 = 4

 

[Gary''s Student]

=COUNTIF(A1:G1,">"&3.7)+COUNTA(A1:G1)-COUNT(A1:G1)

 

[Marcelo]

one simple way

=COUNTIF(D511,3.7)+COUNTIF(D511,"<5")

d5:d11 is a sample, use your data range

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Ecoman" escreveu:

 

[Gaurav]

try this

=SUMPRODUCT(--(A1:G1<3.7)--(A1:G1="<5"))

 

[Ecoman]

It's close, but not quite there.

Here's the real data if it helps.

1.3 32 <0.5 6.4 15.7 12.8 58 50 <5 21 45 <5 34 13 <5 <5 <5 <5 <5 <5 <5 <5 <5

That should be 21

 

[Ecoman]

It works for some of the data, however I have values of <0.5 that get
included as part of this formula.

Thank you for the quick response.