Simplfying a multi-column SUMPRODUCT equation

[matt]

Hey...who can help me out.

I'm trying to use SUMPRODUCT to sum the products of a row
meeting criteria. However, I need to do this twice in
many rows...se example:

A B C D E
1 0 1 1 30
0 1 1 0 40
1 1 1 1 20

What I need is a formula that will give me (A*C*D*E)+
(B*C*D*E) and sum for all rows. However, my REAL data
contain many many more columns and doing the REAL formula
this way puts me above the formula character limit.

What I'm considering is something that would work more
like ((A+B)*C*D*E) and sum for all rows ...as this looks
like it is shorter in theory. But I can't make this work
with SUMPRODUCT. Anyone have ideas???

thx
-matt

 

[Harlan Grove]

...
...

What I'm considering is something that would work more
like ((A+B)*C*D*E) and sum for all rows ...as this looks
like it is shorter in theory. But I can't make this work
with SUMPRODUCT. Anyone have ideas???

This should work. So the problem very likely rests in your actual ranges. What's
the *real* formula you're trying to use or the range references or expressions
for A through E?

 

[Karen]

Not sure if this will help, but you might want to try with
an array-entered equation. You can do SUMPRODUCT with
criteria as follows:

{=SUM(A1:A100*B1:B100*(C1:C100=1)*(D1100="A"))}

(the brackets { } indicate that this is an array -> enter
with control-shift-enter - don't actually type the
brackets)

This will do SUMPRODUCT for A1:A100 * B1:B100 when column
C = 1 and D = A. You can add in more criteria or more
columns to be sum-producted. Maybe this will help reduce
the number of characters you're using?? Play around with
it a little - doesn't hurt to try, right?

Hope that helps. Good luck,
Karen