Formula Problem
 

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

Formula Problem
 

SUMPRODUCT(--($A$1:$A$20000=I5606),--(E$1:E$20000=1),$D$1:$D$20000)
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"cherman" wrote in message
...
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

Formula Problem
 

Try

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

Hope this helps,

Hutch

"cherman" wrote:

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

Formula Problem
 

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!

"Tom Hutchins" wrote:

Try

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

Hope this helps,

Hutch

"cherman" wrote:

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

Formula Problem
 

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"),



--
Jacob


"cherman" wrote:

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!

"Tom Hutchins" wrote:

Try

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

Hope this helps,

Hutch

"cherman" wrote:

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

Formula Problem
 

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.

--
Biff
Microsoft Excel MVP


"cherman" wrote in message
...
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!

"Tom Hutchins" wrote:

Try

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

Hope this helps,

Hutch

"cherman" wrote:

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