# SUMPRODUCT Not Working

I am really stuck with this one.....

=SUMPRODUCT(CABLE!\$B\$5:\$B\$5153='Qty Terminations'!C3,((CABLE!\$J\$5:\$J\$5153+CABLE!\$P\$5:\$P\$5153)*CABLE!\$I\$5:\$I\$5153))

The value being look up are in a column B
Then start the crazy part.
For each match in column B,
(Value in column J + Value in column P) * Value in Column I
This needs to add multiple rows if the match occurs numerous times.
Ejgil Hedegaard

CABLE!\$B\$5:\$B\$5153='Qty Terminations'!C3 returns True/False
To convert to 1/0 for multiplication use
--(CABLE!\$B\$5:\$B\$5153='Qty Terminations'!C3)

Change to
``=SUMPRODUCT(--(CABLE!\$B\$5:\$B\$5153='Qty Terminations'!C3),CABLE!\$J\$5:\$J\$5153+CABLE!\$P\$5:\$P\$5153,CABLE!\$I\$5:\$I\$5153)``
You could also use
``=SUMPRODUCT((CABLE!\$B\$5:\$B\$5153='Qty Terminations'!C3)*((CABLE!\$J\$5:\$J\$5153+CABLE!\$P\$5:\$P\$5153)*CABLE!\$I\$5:\$I\$5153))``
then CABLE!\$B\$5:\$B\$5153='Qty Terminations'!C3 with True/False works as if it is 1/0.
but sumproduct without * should be slightly faster.
DougDodge

Hmmm.

=SUMPRODUCT(--(CABLE!\$B\$5:\$B\$5153='Qty Terminations'!C3),CABLE!\$J\$5:\$J\$5153+CABLE!\$P\$5:\$P\$5153,CABLE!\$I\$5:\$I\$5153) Gives me #N/A

=SUMPRODUCT((CABLE!\$B\$5:\$B\$5153='Qty Terminations'!C3)*((CABLE!\$J\$5:\$J\$5153+CABLE!\$P\$5:\$P\$5153)*CABLE!\$I\$5:\$I\$5153)) Gives me #VALUE!

Maybe it could be my data......

Let me try it on some test data......