=Countif Question

[lhather]

Hi, I've searched though and have not found the answer that is working for me on this question.

I have a range of number in column F. The Values range from -24,000 to +5,000.

I would like to use the countif formula to find how many numbers are between the value of 0 to +500. I have lots of different ranges I want to specify.

I did try a couple of formulas from this board but they didn't work in my situation. Thanks in advance as usual.

I also would like to use a sumif function with the same range.

 

[asad]

May be something like this

=sumproduct(--(F1:F200>0),--(F1:F200<500))

for counting number of cells

 

[asad]

And

=sumproduct(--(F1:F200>0),--(F1:F200<500),(F1:F200))

for summing all those numbers that match criteria

 

[lhather]

Thanks! that worked.

 

[Peter_SSs]

Hi, I've searched though and have not found the answer that is working for me on this question.

I have a range of number in column F. The Values range from -24,000 to +5,000.

I would like to use the countif formula to find how many numbers are between the value of 0 to +500.

You can do this with COUNTIF and it is faster than SUMPRODUCT which may become an issue if you have a large set of data.

=COUNTIF(F1:F200,"<500")-COUNTIF(F1:F200,"<=0")

Adjust the < and <= depending on just what you want to happen at the boundaries.