How can I Count # cells in specified range with value range?

[Mike666]

I'm trying to count the number of cells in a column less than a value but greater than 0.

I would have thought the function should look like:

=COUNTIF(C:C, <=21 & >0)

However the operators doesn't seem to be acceptable within this format. They are displayed as a textual output. I've also tried:

=COUNTIF(C:C, 1:21)

and also creating a named label for the acceptable range of values

=COUNTIF(C:C, SOON)

where soon is a table with values 1 through 21

I realise I could do this by specifying a list of COUNTIF functions with each of the individual desired values and returning the sum to my target cell but there's got to be a more elegant solution. What if the range was 40000? I'm out of ideas so I'd appreciate anybody who can help. It doesn't seem like a tough problem but I've no idea as to the solution.

 

[Jon von der Heyden]

Try:

=SUMPRODUCT(--(C1:C100>0),--(C1:C100<=21))

Don't use whole columns in SUMPRODUCT (C:C).

Regards
Jon

 

[Richard Schollar]

=COUNTIF(C:C,">0")-COUNTIF(C:C,">21")

 

[Aladin Akyurek]

Re: How can I Count # cells in specified range with value ra

I'm trying to count the number of cells in a column less than a value but greater than 0.

I would have thought the function should look like:

=COUNTIF(C:C, <=21 & >0)

However the operators doesn't seem to be acceptable within this format. They are displayed as a textual output. I've also tried:

=COUNTIF(C:C, 1:21)

and also creating a named label for the acceptable range of values

=COUNTIF(C:C, SOON)

where soon is a table with values 1 through 21

I realise I could do this by specifying a list of COUNTIF functions with each of the individual desired values and returning the sum to my target cell but there's got to be a more elegant solution. What if the range was 40000? I'm out of ideas so I'd appreciate anybody who can help. It doesn't seem like a tough problem but I've no idea as to the solution.

=COUNTIF(C:C,">0")-COUNTIF(C:C,">21")

With a range housing 1 to 21, which is named, say, List...

=SUMPRODUCT(COUNTIF(C:C,List))

 

[Mike666]

Thanks to you all.