EXCEL COUNTIF problem

[andyp161]

I want to count the number of cells that contain numbers greater than
1930 and less that 1940. The following does not seem to work:
=COUNTIF($C$2:$C$2502,">1930","<1940"). Any suggestions??

Kind regards

 

[Norman Harker]

Hi Andyp161!

One approach:

=COUNTIF(A1:A29,">1930")-COUNTIF(A1:A29,">=1940")

Another:

=SUMPRODUCT(--(A1:A29>1930),--(A1:A29<1940))

The -- coerces Boolean returns of TRUE and FALSE from the bracketted
expressions into 1 and 0.

 

[Andy B]

Hi

You can either use 2 COUNTIFs:
=COUNTIF($C$2:$C$2502,">1930")-COUNTIF($C$2:$C$2502,">1940")
or you could try:
=SUMPRODUCT(($C$2:$C$2502 >1930)*($C$2:$C$2502 <1940)*($C$2:$C$2502))

 

[Andy B]

Hold on!! My second formula sums the figures!. Try this:
=SUMPRODUCT(($C$2:$C$2502 >1930)*($C$2:$C$2502 <1940))

--
Andy.

Hi

You can either use 2 COUNTIFs:
=COUNTIF($C$2:$C$2502,">1930")-COUNTIF($C$2:$C$2502,">1940")
or you could try:
=SUMPRODUCT(($C$2:$C$2502 >1930)*($C$2:$C$2502 <1940)*($C$2:$C$2502))