Another counting question!

[JRD]

I need to be able to count the number of cells in a column containing certain
text words, but only if cells in the same row in 2 other columns contain
certain text

I have put an example below:

A B C
High Invalid Death
Very Low Invalid MI
Low Valid CVA
Low Invalid No Complications
Low Valid No Complications
Very Low Valid MI, Death
Very Low Invalid No Complications
Low Valid Emergency CABG
Very Low Invalid No Complications
Very Low Invalid Death
Low Invalid Death
Very Low Invalid CVA, Death

So, I need to count the number of cells in column C that contain either
"Death", "CVA", "MI", or "Emergency CABG" or any combination the 4, but only
if the cell in the same row in column A = "Low" and the cell in the same row
in column B = "Valid". Therefore the answer here would be 2 (3rd and 8th
rows).

Is this possible?

Thanks

John

 

[Max]

One way:
=SUMPRODUCT((ISNUMBER(MATCH($C$1:$C$12,{"Death";"MI";"CVA";"Emergency
CABG"},0)))*($A$1:$A$12="Low")*($B$1:$B$12="Valid"))
Adapt the ranges to suit

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(A1:A12="Low"), --(B1:B12="Valid"),
--(ISNUMBER(SEARCH("CVA",C1:C12)) + ISNUMBER(SEARCH("MI",C1:C12)) +
ISNUMBER(SEARCH("Death",C1:C12)) + ISNUMBER(SEARCH("Emergency
CABG",C1:C12)) > 0))

Given your data set, it would be simpler to use

=SUMPRODUCT(--(A1:A12="Low"), --(B1:B12="Valid"), --(C1:C12<>"No
Complications"))

 

[JE McGimpsey]

Note that this will not meet the "or any combination of the 4" criterion.

 

[Max]

Note that this will not meet the "or any combination of the 4" criterion.

My oversight there.
Pl dismiss the suggestion.

---