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.
DougDodgeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
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)

Open in new window

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

Open in new window

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.
0
DougDodgeAuthor Commented:
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......
0
DougDodgeAuthor Commented:
Your formulas worked..... Who would have thought that few blank cells formatted as text would have caused me so much grief....

Thank you for your formulas.... They worked perfectly after I normalized my data....
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DougDodgeAuthor Commented:
Formulas were perfect for the problem.... Additionally offering a second option was a bonus.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office Productivity

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.