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

One way:
=SUMPRODUCT((ISNUMBER(MATCH($C$1:$C$12,{"Death";"M I";"CVA";"Emergency
CABG"},0)))*($A$1:$A$12="Low")*($B$1:$B$12="Valid" ))
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"JRD" wrote:
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

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"))

In article ,
JRD wrote:

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

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


In article ,
Max wrote:

One way:
=SUMPRODUCT((ISNUMBER(MATCH($C$1:$C$12,{"Death";"M I";"CVA";"Emergency
CABG"},0)))*($A$1:$A$12="Low")*($B$1:$B$12="Valid" ))
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"JRD" wrote:
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

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

My oversight there.
Pl dismiss the suggestion.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---