Formula Problem

[cherman]

I am trying to sum one column when another column = a certain value and when
a 2nd column = a certain value. Here is the formula I have so far. However,
it is summing everything in column D. Any suggestions would be greatly
appreciated, even if it is a completely different formula.

=IF(SUMPRODUCT(--($A$1:$A$20000=I5606),--(E$1:E$20000=1)),SUM($D$1:$D$20000),0)

Thanks,
Clint

 

[Bernard Liengme]

SUMPRODUCT(--($A$1:$A$20000=I5606),--(E$1:E$20000=1),$D$1:$D$20000)
best wishes

 

[Tom Hutchins]

Try

=SUMPRODUCT(--($A$1:$A$20000=I5606),--(E$1:E$20000=1),($D$1:$D$20000))

Hope this helps,

Hutch

 

[cherman]

Thank you very much! That was exactly what I was looking for.

One last question. I tried to replace the column references as they are with
total column references, using A:A instread of $A$1:$A$20000, but I get a
#NUM! error. Can you tell me how to do this?

Thanks again!

 

[Jacob Skaria]

You must be using XL2003. SUMPRODCT() will return an error if you reference
the entire column. Instead try

If you have headers in row 1, you could use:
--($A$2:$A$65536="something"), ...

or just ignore the final row

--($A$1:$A$65535="something"),

 

[T. Valko]

You can't use entire columns as range references with SUMPRODUCT unless
you're using Excel 2007. Use a smaller specifc range. You can use up to the
entire column minus 1 row:

A1:A65535
A2:A65536

However, *every* cell referenced in SUMPRODUCT (and other array formulas)
will be calculated. If don't have data in *every* one of those cells then
you're wasting calculation resources. For example, your data goes to A10000.
If you use A2:A65536 as the range in the formula with A10001 to A65536 being
empty cells, you're wasting resources by calculating 55536 empty cells.