I have in a spreadsheet a summary of different items that we would purchase in a package. What I'd like to do is lookup a particular name with a lookup table in a formula and sum what the cost would be if all the pieces were purchased separate vs in the package to see the savings.
The formula I'd like to be in Column R of Kits. The parts and prices for the lookup table to reference are in the tab Parts & Prices. I do realize I have some parts to add to the lookup list.
Something along the lines of:
=SUMPRODUCT((E2:P2='Parts & Price'!A$2:A$34)*('Parts & Price'!B$2:B$34)) and copy down
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
Great this works. If I was to build from scratch how would I do so with the formula builder?
Formula builder ewwwwwww.
I can explain it to you, but I've never used Formula Builder before and I've been using Excel for 8 years.
=SUMPRODUCT((criteria range = criteria) * (value range))
I've attached a SUMPRODUCT tutorial I wrote some time ago.
If you still can't get it, you can always just use SUMIF. You'll just need to put SUMPRODUCT around the formula to handle the multicellular criteria.
=SUMPRODUCT(SUMIF('Parts & Price'!A$2:A$34,E2:P2,'Parts & Price'!B$2:B$34))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks