Microsoft Office Forums Formula to depend on multiple cell-values
 Register FAQ Search Today's Posts Mark Forums Read

#1
05-19-2017, 01:19 PM
 Challebjoern Windows 10 Office 2007 Novice Join Date: May 2017 Posts: 6
Formula to depend on multiple cell-values

Hey guys.

Another problem I can't solve, but I guess it's kinda simple.

Been trying with vLookup but doesn't get it to work

I want cell H10 to check what value cell B10 has (there are 6 different ones cuz it's a dropdownlist). And also check what value cell D10 got. And from that, evaluate the value to be shown in cell H10. The data it will check for is on Sheet3 "DATA".
For example. If B10 = Tidningar & D10 = 370L. H10 should show the result of DATA!D17 - "81". ref. (B10 = Tidningar 'Check Sheet3 -> A16' => then check D10 = 370L 'Check Sheet3 -> B17 => H10 = 81)

If they collumns or so are missplaced, feel free to put them in the right order

The DATA sheet3, has all the rawdata for my formulas. Why I want it to depend on B10 is beacuse it's not yet complete and the other ones got different prices.

Is it possible to do this?
Attached Files
 Macro-Test.xls (223.0 KB, 3 views)
#2
05-20-2017, 10:20 AM
 NoSparks Windows 7 64bit Office 2010 64bit Excel Hobbyist Join Date: Nov 2013 Location: British Columbia, Canada Posts: 549

Quote:
 Been trying with vLookup but doesn't get it to work
Have you tried completing column "A" of that table portion of the Data sheet and using a SumProduct formula ?
Code:
`=SUMPRODUCT((Data!\$A\$16:\$A\$37=Kalkyl!\$B10)*(Data!\$B\$16:\$B\$37=Kalkyl!\$D10)*(Data!\$D\$16:\$D\$37))`
#3
05-20-2017, 11:19 AM
 Challebjoern Windows 10 Office 2007 Novice Join Date: May 2017 Posts: 6

Quote:
 Originally Posted by NoSparks Have you tried completing column "A" of that table portion of the Data sheet and using a SumProduct formula ? Code: `=SUMPRODUCT((Data!\$A\$16:\$A\$37=Kalkyl!\$B10)*(Data!\$B\$16:\$B\$37=Kalkyl!\$D10)*(Data!\$D\$16:\$D\$37))`
Wow, just completed it and it worked like a charm with "Sumproduct". Thanks alot man!
Didn't know I could use that one. ++++++!
#4
05-20-2017, 12:20 PM
 jeffreybrown Windows Vista Office 2007 Expert Join Date: Apr 2016 Posts: 381

If you're interested in a reference...

Here's at least one...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
#5
05-23-2017, 04:04 AM
 Challebjoern Windows 10 Office 2007 Novice Join Date: May 2017 Posts: 6

Quote:
 Originally Posted by jeffreybrown If you're interested in a reference... Here's at least one... http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Sweet, I'll read through it! Thanks.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post EtanM Excel Programming 3 04-11-2016 01:43 AM Jac_Fcb Excel 1 03-17-2015 12:41 AM AUHAMM Excel 3 10-27-2014 09:11 PM iuliandonici Excel 1 04-13-2011 09:45 PM iuliandonici Excel 4 04-13-2011 09:27 PM

All times are GMT -7. The time now is 01:43 PM.

 -- Default Style -- Mobile Style Contact Us - Privacy Statement - Top