Formula Problem

  • Thread starter Thread starter cherman
  • Start date Start date
C

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
 
Try

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

Hope this helps,

Hutch
 
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!
 
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"),
 
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.
 
Back
Top