Can I use SUMPRODUCT (or something else) to make my life easier?

Please see the attached, the desired result of the SUMPRODUCT function is in the yellow cell. Just can't seem to get it to work with the subtraction of the Cost cell that is not set up in a similar sized array.... Real data is 1000 records..... so i'm really hoping to automate this a bit.
SumProductEE.xlsx
LVL 1
-PolakAsked:
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.

-PolakAuthor Commented:
I believe I have a valid solution by making my Cost array the same size. It's not the cleanest solution by any means because, I will have to duplicate that INDEX and MATCH formula by 1000 Records * 21 Types; thereby bogging down the workbook a lot.

If you have a better solution please let me know. My solved version is attached.
SumProductEE-Solved-Polak.xlsx
0
Martin LissOlder than dirtCommented:
Would a VBA solution be acceptable? If so will the data headers always be where they are? Please also verify that there will be an unknown number of rows following that, and that at the end will be the "cost" row, and that the "Cost Lookup table" will be in the same relative position.
0
-PolakAuthor Commented:
Hi Martin, no a VBA solution won't do it in this case, I've been building the structure all day for a very complex optimization/solver problem. As of right now I've just created a 3rd array to the right of the first two and included it in the SumProduct formula. Lots of Columns but it works. If there is no way to improve my Solved workbook I'll go head and mark the question closed. Thanks for trying!
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Martin LissOlder than dirtCommented:
Sorry, but I'm not a "formula guy" so I can't answer that question.
0
-PolakAuthor Commented:
I've requested that this question be deleted for the following reason:

Went with a different Approach.
0
Rory ArchibaldCommented:
Isn't it just:

=SUMPRODUCT(Table2[[A]:[U]],Table2[[A2]:[U2]])-SUMPRODUCT(G7:AA7*Table2[[A]:[U]])

Open in new window

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
-PolakAuthor Commented:
I honestly can't remember what wasn't working for me at this point, that solution does what I posted in the example workbook so I'll accept.
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
Microsoft Excel

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.