=COUNTIF with both AND and OR functions

Copper Contributor

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



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




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.