How do I collect a sum with the following conditions(?):
If A:A = "AAA"
If D: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: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

Try,

=SUMPRODUCT((A1:A20000="XXX")*(D1:D20000="XXX"))

and

=SUMPRODUCT((A1:A20000="XXX")*(C1:C20000="BBB")*(D 1:D20000="XXX"))

You can't use full columns

Mike


"psalm91jim" wrote:

How do I collect a sum with the following conditions(?):
If A:A = "AAA"
If D: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: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

Do you want a COUNT or a SUM?

Your description sounds like a COUNT.

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

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

Better to use cells to hold the criteria:

X1 = AAA
X2 = BBB
X3 = XXX

=SUMPRODUCT(--(A1:A10=X1),--(D1:D10=X3))

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

--
Biff
Microsoft Excel MVP


"psalm91jim" wrote in message
...
How do I collect a sum with the following conditions(?):
If A:A = "AAA"
If D: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: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