# =COUNTIF with both AND and OR functions

Copper Contributor

# =COUNTIF with both AND and OR functions

How can I count multiple criteria from multiple columns but one of the columns can be any of several values?

I'm trying to count the reason for Non-diagnostic specimens defined within a final diagnosis string and categorize them by Location (Value_A) and Specimen Type (Value_B), and also the reason for the Non-Diagnosis, which may be described several ways containing words such as acellular, hypocellular, insufficient material, etc.

My Example

=COUNTIFS(F:F, B1, D:D, A5, K:K, "*Acellular*"  // OR   K:K="*Hypocellular*"   OR    K:K ="*insufficient*"

where:  F:F =Value_A (B1)   [AND]    D:D =Value_B (A5)   [AND]  K:K contains item in list (acellular, hypocellular, insufficient

The first portion works, but I have tried several ways to incorporate the other two scenarios without luck.

I tried COUNTIF  (F:F, B1, D:D, A5, K:K, "*Acellular*") + COUNTIF(F:F, B1, D:D, A5, K:K, "*insufficent*")  etc, but it may double count if more acellular and insufficient are both in that text string.

I also tried SUM(COUNTIF(        but I cant seem to get that one to work correctly either.

2 Replies

# Re: =COUNTIF with both AND and OR functions

``=SUM((D:D=A5)*(F:F=B1)*(ISNUMBER(SEARCH({"Hypocellular","acellular","insufficient"},K:K))))``

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

# Re: =COUNTIF with both AND and OR functions

I'd use a finite range for the following.

=SUMPRODUCT((\$F\$1:\$F\$500=B1)*(\$D\$1:\$D\$500=A5)*((ISNUMBER(SEARCH("Hypocellular", \$K\$1:\$K\$500))+ISNUMBER(SEARCH("insufficient", \$K\$1:\$K\$500)))>0))

Adjust the ranges as needed but don't use entire columns for perfomance reasons.