conditional sum(if) data matching two (or more) separated columns

[psalm91jim]

How do I collect a sum with the following conditions(?):
If A:A = "AAA"
If D = "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 A:A = "AAA" and,
If C:C = "BBB" and,
If D= "XXX" then,
sum of all "XXX" that are also "AAA" and "BBB" when "AAA" and "BBB" appear
in the same row with "XXX"

Thanks Jim

 

[Mike H]

Try,

=SUMPRODUCT((A1:A20000="XXX")*(D120000="XXX"))

and

=SUMPRODUCT((A1:A20000="XXX")*(C1:C20000="BBB")*(D120000="XXX"))

You can't use full columns

Mike

 

[T. Valko]

Do you want a COUNT or a SUM?

Your description sounds like a COUNT.

=SUMPRODUCT(--(A1:A10="AAA"),--(D110="XXX"))

=SUMPRODUCT(--(A1:A10="AAA"),--(C1:C10="BBB"),--(D110="XXX"))

Better to use cells to hold the criteria:

X1 = AAA
X2 = BBB
X3 = XXX

=SUMPRODUCT(--(A1:A10=X1),--(D110=X3))

=SUMPRODUCT(--(A1:A10=X1),--(C1:C10=X2),--(D110=X3))

 

[psalm91jim]

Hi Jim

=SUMPRODUCT(--(A1:A1000)="AAA"),--(C1:C1000="BBB"),--(D11000="XXX"))

Thanks for your quick response Roger but that formula generated for me #NUM.
Perhaps 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 responses (depending on what the column is
requiring within the parameters set for the appropriated--and only--answer).

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)

 

[Roger Govier]

Hi Jim

That should have produce a correct result provided your data is OK.
You would have needed to make the ranges fit your requirement, (you cannot
use whole columns) and each range would have to have the same dimensions.
Post back with the actual formula you used.

if you are still having difficulties, mail me a copy of the workbook direct
and I will take a look.
To send direct
roger at technology4u dot co dot uk
Change the at and dots to make a valid email address,