Forum Discussion
=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.
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.
=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.
