EXCEL COUNTIF problem

  • Thread starter Thread starter andyp161
  • Start date Start date
A

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

--
Andy.


Andy B said:
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))
Click to expand...
 
Back
Top