How do I collect a sum with the following conditions(?):
If found in column A:A = all cells containing "AAA"
If found in column D:D = all cells containing "XXX"
then sum of all "XXX" that also are "AAA" (i.e., appear in the same row as
"AAA")

Along the same lines consider also:
If found in column A:A = all cells containing "AAA" and,
If found in column C:C = all cells containing "BBB" and,
If found in column D:D= all cells containing "XXX" then,
sum of all "XXX" that are also "AAA" and "BBB" when "AAA" and "BBB" appear
in the same row with "XXX"

If I was not clear in describing the scenario. Let me try to show it a
different way:

Out of a possible 200+ columns of data on patients, I want to find how many
patients meet the following criteria: How many patients that are Pulmonary
consults were treated in a timely manner while in ICU.

One of the columns contains which service referred the consult. For
instance, column AI lists by patient who referred the consult: Cardiology,
Oncology, Pulmonary, etc., as text data. Another column (BB), contains text
data which describes how timely medical attention was received by patient:
Some/Timely, Full/Timely, Minimal, etc. And finally another column (GH)
contains text data which describes in what department the patients first were
seen: ICU,
PCU, Nursing Floor, etc. Each of these columns may have the possibility of
being answered by one of 4-20 (pre-established/restricted to only) responses.

From the three columns I want to find the number of patients that meet the
following condition: AI="Pulmonary"; BB="Full/Timely"; and GH="ICU".

The answer should reveal a sum of patient meeting those conditions from a
pool of all patients seen in a fiscal year. For instance, though I may have
seen 1500 patients in Fiscal Year 2008 (each row is a different patient),
only 45 met the conditions stated above: They were referred to my department
by "pulmonary" MD, their symptoms were addressed in a "full/timely" manner
while they were in the care of the "ICU" department.

I have created a number of charts that track compilation of data from the
large fiscal year spreed sheet and this formula is for one item in a
particular chart.

I appreciate your help. For the past few years I have attempted to find an
answer to this query but have been unable to. Looked in various help books
but nothing seems to address this particular conditional need (or at least I
have been unable to find it).

Thanks for your kind help
Jim Bowers (psalm91jim)

"psalm91jim" wrote:


How do I collect a sum with the following conditions(?):
If found in column A:A = all cells containing "AAA"
If found in column D:D = all cells containing "XXX"
then sum of all "XXX" that also are "AAA" (i.e., appear in the same row as
"AAA")

Along the same lines consider also:
If found in column A:A = all cells containing "AAA" and,
If found in column C:C = all cells containing "BBB" and,
If found in column D:D= all cells containing "XXX" then,
sum of all "XXX" that are also "AAA" and "BBB" when "AAA" and "BBB" appear
in the same row with "XXX"

If I was not clear in describing the scenario. Let me try to show it a
different way:

Out of a possible 200+ columns of data on patients, I want to find how many
patients meet the following criteria: How many patients that are Pulmonary
consults were treated in a timely manner while in ICU.

One of the columns contains which service referred the consult. For
instance, column AI lists by patient who referred the consult: Cardiology,
Oncology, Pulmonary, etc., as text data. Another column (BB), contains text
data which describes how timely medical attention was received by patient:
Some/Timely, Full/Timely, Minimal, etc. And finally another column (GH)
contains text data which describes in what department the patients first were
seen: ICU,
PCU, Nursing Floor, etc. Each of these columns may have the possibility of
being answered by one of 4-20 (pre-established/restricted to only) responses.

From the three columns I want to find the number of patients that meet the
following condition: AI="Pulmonary"; BB="Full/Timely"; and GH="ICU".

The answer should reveal a sum of patient meeting those conditions from a
pool of all patients seen in a fiscal year. For instance, though I may have
seen 1500 patients in Fiscal Year 2008 (each row is a different patient),
only 45 met the conditions stated above: They were referred to my department
by "pulmonary" MD, their symptoms were addressed in a "full/timely" manner
while they were in the care of the "ICU" department.

I have created a number of charts that track compilation of data from the
large fiscal year spreed sheet and this formula is for one item in a
particular chart.

I appreciate your help. For the past few years I have attempted to find an
answer to this query but have been unable to. Looked in various help books
but nothing seems to address this particular conditional need (or at least I
have been unable to find it).

Thanks for your kind help
Jim Bowers (psalm91jim)

Also this previously suggested formula does not work:
=SUMPRODUCT(--(AI:AI)="pulmonary")--(BB:BB="full/timely")--(GH:GH="ICU"));
it's result = #NUM

"psalm91jim" wrote:


How do I collect a sum with the following conditions(?):
If found in column A:A = all cells containing "AAA"
If found in column D:D = all cells containing "XXX"
then sum of all "XXX" that also are "AAA" (i.e., appear in the same row as
"AAA")

Along the same lines consider also:
If found in column A:A = all cells containing "AAA" and,
If found in column C:C = all cells containing "BBB" and,
If found in column D:D= all cells containing "XXX" then,
sum of all "XXX" that are also "AAA" and "BBB" when "AAA" and "BBB" appear
in the same row with "XXX"

If I was not clear in describing the scenario. Let me try to show it a
different way:

Out of a possible 200+ columns of data on patients, I want to find how many
patients meet the following criteria: How many patients that are Pulmonary
consults were treated in a timely manner while in ICU.

One of the columns contains which service referred the consult. For
instance, column AI lists by patient who referred the consult: Cardiology,
Oncology, Pulmonary, etc., as text data. Another column (BB), contains text
data which describes how timely medical attention was received by patient:
Some/Timely, Full/Timely, Minimal, etc. And finally another column (GH)
contains text data which describes in what department the patients first were
seen: ICU,
PCU, Nursing Floor, etc. Each of these columns may have the possibility of
being answered by one of 4-20 (pre-established/restricted to only) responses.

From the three columns I want to find the number of patients that meet the
following condition: AI="Pulmonary"; BB="Full/Timely"; and GH="ICU".

The answer should reveal a sum of patient meeting those conditions from a
pool of all patients seen in a fiscal year. For instance, though I may have
seen 1500 patients in Fiscal Year 2008 (each row is a different patient),
only 45 met the conditions stated above: They were referred to my department
by "pulmonary" MD, their symptoms were addressed in a "full/timely" manner
while they were in the care of the "ICU" department.

I have created a number of charts that track compilation of data from the
large fiscal year spreed sheet and this formula is for one item in a
particular chart.

I appreciate your help. For the past few years I have attempted to find an
answer to this query but have been unable to. Looked in various help books
but nothing seems to address this particular conditional need (or at least I
have been unable to find it).

Thanks for your kind help
Jim Bowers (psalm91jim)

THIS IS THE FORMULA THAT SEEMS TO WORK:
=SUMPRODUCT(--(AI1:AI46000="pulmonary"),--(GH1:GH46000="icu"),--(BB1:BB46000="full/timely"))