asked on

Count with OR in Excel

This is probably ridiculously easy, and I'm complicating it unnecessarily. I have a data range of p2:p32 that contains values from -200% to + 1600%. I am trying to count those cells that are greater than 25% or less than -25%. When I use countif and put the first criteria in, it calculates successfully. When I add the second, it returns 0. Is countif an AND function?

What would be the correct formula for this?

Thanks,

Michael

Shums Faruk

Hi Try below:

=COUNTIF(P:P,">=25")+COUNTIF(P:P,"<=-25")

Open in new window

or:

=SUM(COUNTIF(P:P,">=25"),COUNTIF(P:P,"<=-25"))

Open in new window

aikimark

If you're using a current version of Excel, consider using the CountIfs() function. It's a bit cleaner.
https://support.office.com/en-us/article/COUNTIFS-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842

Glenn Ray

The above solutions are valid. Here's another using an array function (use [Shift]+[Ctrl]+[Enter]):
=SUM(--(ABS(P2:P32)>25))

Regards,
Glenn

Glenn Ray

A single COUNTIFS function will not work in this case since the criteria requested are exclusive (i.e., < -25 OR > 25). The COUNTIFS uses AND logic.

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Michael Paxton

ASKER

Excellent, easily implemented solution

Shums Faruk

You're Welcome! Pleased to help