Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 05-19-2017, 01:19 PM
Challebjoern Challebjoern is offline Windows 10 Office 2007
Novice
 
Join Date: May 2017
Posts: 6
Challebjoern is on a distinguished road
Default 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
File Type: xls Macro-Test.xls (223.0 KB, 3 views)
Reply With Quote
  #2  
Old 05-20-2017, 10:20 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Posts: 330
NoSparks is on a distinguished road
Default

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))
Reply With Quote
  #3  
Old 05-20-2017, 11:19 AM
Challebjoern Challebjoern is offline Windows 10 Office 2007
Novice
 
Join Date: May 2017
Posts: 6
Challebjoern is on a distinguished road
Default

Quote:
Originally Posted by NoSparks View Post
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. ++++++!
Reply With Quote
  #4  
Old 05-20-2017, 12:20 PM
jeffreybrown jeffreybrown is offline Windows Vista Office 2007
Expert
 
Join Date: Apr 2016
Posts: 313
jeffreybrown is on a distinguished road
Default

If you're interested in a reference...

Here's at least one...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Reply With Quote
  #5  
Old 05-23-2017, 04:04 AM
Challebjoern Challebjoern is offline Windows 10 Office 2007
Novice
 
Join Date: May 2017
Posts: 6
Challebjoern is on a distinguished road
Default

Quote:
Originally Posted by jeffreybrown View Post
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.
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find and replace multiple values, according to table value - excel formula EtanM Excel Programming 3 04-11-2016 01:43 AM
IF Formula - multiple values Jac_Fcb Excel 1 03-17-2015 12:41 AM
I need to add multiple values based on multiple criteria in a cell not sure what to do AUHAMM Excel 3 10-27-2014 09:11 PM
How to count multiple values in a single cell, except zero? iuliandonici Excel 1 04-13-2011 09:45 PM
Sum & difference between multiple values in a single cell iuliandonici Excel 4 04-13-2011 09:27 PM


All times are GMT -7. The time now is 01:44 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft