Count.if

[Petrus Van den Cruyce]

I'm trying to count the number of cell that contain a value between 2
value's stored in two cells. I tried count.if(matrix;"<cellname") to
try the number off cell with a value smaller than the value in cellname.
This is not working. How should i count en what whit my initial
problem of counting the numler of cells within a range that is stored
in two cells?

 

[JE McGimpsey]

One way:

Assume the lower value is in C1, the upper value in C2, and the range to
search is A1:A1000:


=COUNTIF(A1:A1000, ">=" & C1) - COUNTIF(A1:A1000, ">" & C2)

alternatively:

=SUMPRODUCT(--(A1:A1000>=C1), --(A1:A1000<=C2))

For the purpose of "--", see

http://www.mcgimpsey.com/excel/doubleneg.html

 

[Petrus Van den Cruyce]

Thanks a lot for your solution, it is doing his job well.

From Brussels Belgium