COUNTIF Function

  • Thread starter Thread starter Josh
  • Start date Start date
J

Josh

How can I use and AND OR statement in the criteria for
the COUNTIF function? Specifically I want to count the
number of times a number appears within a specific
range. For example...

COUNTIF(A2:A950,>=0 and <=50000)

Please help!

Thanks!
 
Hi
use SUMPRODUCT:
=SUMPRODUCT((A2:A950>=0)*(A2:A950<=50000))

or
=SUMPRODUCT(--(A2:A950>=0),--(A2:A950<=50000))
 
Hi Josh!

Short answer is that you can't.

But you could use:

=COUNTIF(A2:A950,">="&0)-COUNTIF(A2:A950,">"&50000)

Or use SUMPRODUCT:

=SUMPRODUCT(--(A2:A950>=0),--(ISBLANK(A2:A950)=FALSE),--(A2:A950<=50000))

The three structures in SUMPRODUCT are statements that return TRUE or
FALSE. The -- coerces these to 1 and 0 and only if all three are 1
will the sum added to the product be 1*1*1.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Frank!

I got trouble with both of those when there were blank cells in the
range.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thanks Frank!!

-----Original Message-----
Hi
use SUMPRODUCT:
=SUMPRODUCT((A2:A950>=0)*(A2:A950<=50000))

or
=SUMPRODUCT(--(A2:A950>=0),--(A2:A950<=50000))


--
Regards
Frank Kabel
Frankfurt, Germany


.
Click to expand...
 
Hi Norman
got point. for the OP you may use
=SUMPRODUCT(--(A2:A950>=0),--(A2:A950<=50000),--(A2:A950<>""))
 
Back
Top