+ Reply to Thread
Results 1 to 16 of 16

Multiple area LOOKUP + SUM value

  1. #1
    Registered User
    Join Date
    11-08-2018
    Location
    Sacramento CA
    MS-Off Ver
    office 365
    Posts
    7

    Multiple area LOOKUP + SUM value

    I love VLOOKUP, but when I have one specific item name repeated in a table, I don't know how to

    Capture.PNG

    Can someone give me awesome tip?

    In file or image, I want to get Total run time of work center (Line1/Line2) for product A and B.
    Attached Files Attached Files
    Last edited by kimgag; 11-09-2018 at 12:23 PM.

  2. #2
    Registered User
    Join Date
    11-08-2018
    Location
    Sacramento CA
    MS-Off Ver
    office 365
    Posts
    7

    Re: Multiple area LOOKUP + SUM value

    Question should be: What is January's Line1 total run time, and Line3? what about whole year?

    It will be Product A's January Demand "2" multiply by Line1's runtime for A + Product B's January Demand "3" multiply by Line1's runtime for B

    Bascially at C13, I want to know F3*C9+F5*C10 in better layout which I can imply to 10,000 over numbers data table.

  3. #3
    Registered User
    Join Date
    11-08-2018
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    4

    Re: Multiple area LOOKUP + SUM value

    Hi Kimgag,
    I might be misunderstanding your problem, but if you want to use the VLOOKUP on the product range, you can put the 2nd OP in the same row (next) as 1st OP. So you will have just one item to lookup.
    Kimgag.jpgOr use HLOOKUP

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Multiple area LOOKUP + SUM value

    Assuming C13 should be 0.0615, and further assuming that, in reality, you may have many more items to consider than just A & B, and items to exclude etc, you could use something like:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    modify ranges to suit , but note Array entry requirement.

    note also that SUMPRODUCT can be slow when used in large volume, and against very large datasets. Avoid using in conjunction with Conditional Formatting, and volatile functions etc...

  5. #5
    Registered User
    Join Date
    11-08-2018
    Location
    Sacramento CA
    MS-Off Ver
    office 365
    Posts
    7

    Re: Multiple area LOOKUP + SUM value

    XLent, awesome answer. It is working!

    I don't just want to take this tip and know nothing after, so if you could explain how that formula works briefly, it will be appreciated. If no time, it's fine. I will dig by myself.

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Multiple area LOOKUP + SUM value

    @XLent,

    Since your formula as an array formula there is no use of SUMPRODUCT fucntion. Instead, use SUM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  7. #7
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Multiple area LOOKUP + SUM value

    Quote Originally Posted by kimgag View Post
    ...if you could explain how that formula works briefly, it will be appreciated.
    Certainly, breaking into the component parts, using your sample file and cell C13 as example:

    The below will generate a 1-dimensional Boolean array, where True denotes the Line# in your Product key matches the Line# in your calc row:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    the second range returns the associated Run Time for each of the Product / Line # combinations

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    the below generates a 2-dimensional Boolean array, the test being whether or not the Products in the "multiplier lines" match the Product Keys, e.g.:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    so, in other words, you test both rows of your Product Multiplier against each row of your Product / Line # table

    the below generates a 1-dimensional Boolean array (horizontal array), with values being the monthly "multipliers" for each product

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The multiplication of each array coerces the Booleans to their Binary equivalent, which in XL Worksheet terms is 0 for FALSE and 1 for TRUE (in VBA TRUE equates to -1)

    So, post multiplication, you're effectively creating a 2-dimensional array of values -- 1 row per Product / Line #, and 1 column per "Product Multipler"

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    the semi-colon indicates a "row", you have one "row" per Product/Line combo
    the comma indicates a "column", you have two "columns" per row, these reflect the transposed Product Multipiler rows

    so, in the above:

    first "row" you get 0.023 for Product A w/Line 1 and Multiplier of Product A, and 0 for the same Product / Line # but with Product B multiplier (as not Product B)
    second "row" you get 0 for both multipliers given Line # of the Product / Line combo (2) does not match that of the calc row (1)
    third "row" you get 0.0385 for Product B w/Line 1 and Multiplier of Product B, and 0 for the same Product / Line # but with Product A multiplier (as not Product A)
    fourth "row" you get 0 for both multipliers given Line # of the Product / Line combo (2) does not match that of the calc row (1)

    As @AlKey has noted, you could use SUM, rather than SUMPRODUCT, however there will be no performance gain either way, and both can be adjusted to avoid explicit coercion if so required.
    Last edited by XLent; 11-09-2018 at 12:10 PM.

  8. #8
    Registered User
    Join Date
    11-08-2018
    Location
    Sacramento CA
    MS-Off Ver
    office 365
    Posts
    7

    Re: Multiple area LOOKUP + SUM value

    Quote Originally Posted by XLent View Post
    Certainly, breaking into the component parts, using your sample file and cell C13 as example:
    I am facing one problem right now. I have followed the formula in exercise and it was ok. Once I added my 25,000 row/3 column (1st sheet) and 6,000 row/10 column into the formula, it pops the error msg (ran out of resources). I have gone back to check and review what formula I put wrong, nothing came out.. is this something to do with your 1st note (it might slow down if data is large)?

  9. #9
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Multiple area LOOKUP + SUM value

    Yes, Arrays* are resource intensive given their iterative nature and fact that, unlike standard functions, they do not leverage the used range intersect.

    if you can provide an indicative sample of your real life layout we can offer alternatives... counter intuitively it is often better in XL to use "more" lightweight calcs than "fewer" elegant, yet inefficient, ones... i.e. you may find adding a key or two will trivialise your summary calculations.

    Note: SUMPRODUCT is processed like an Array, even without use of TRANSPOSE etc...

  10. #10
    Registered User
    Join Date
    11-08-2018
    Location
    Sacramento CA
    MS-Off Ver
    office 365
    Posts
    7

    Re: Multiple area LOOKUP + SUM value

    Is there any way I can share this file with you? I can't upload this file idk why. it is only 3MB. tried to PM to you, it doesn't go to....

  11. #11
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Multiple area LOOKUP + SUM value

    Save a copy of the file with .xlsb extension -- this will offer better compression and also, bizarrely, is assigned a higher threshold on the upload.

    Include a few expected results, this makes it easier for people to follow / test whilst trying to assist.

  12. #12
    Registered User
    Join Date
    11-08-2018
    Location
    Sacramento CA
    MS-Off Ver
    office 365
    Posts
    7

    Re: Multiple area LOOKUP + SUM value

    Here is the excel file. I have put what I am expecting at "Final" sheet. You would understand fast as you already helped me out once..
    Attached Files Attached Files

  13. #13
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Multiple area LOOKUP + SUM value

    Key question, will it be possible to have the Demand & Labor tabs sorted?

    With large / complex datasets sorting data is often the biggest factor in optimising calculation performance - sorting precedent ranges opens up binary search methods, and binary search methods mean you are only ever working with a tiny fraction of the larger dataset.

  14. #14
    Registered User
    Join Date
    11-08-2018
    Location
    Sacramento CA
    MS-Off Ver
    office 365
    Posts
    7

    Re: Multiple area LOOKUP + SUM value

    Those data can be sorted to some extent as I also pull the data from other raw data using Pivot and filtering.

    I would say Whatever is needed on data sorting can be done pretty much. I have access to them.

  15. #15
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Multiple area LOOKUP + SUM value

    Further to the above, the attached is an extension of your sample file -- the notable changes are as follows:

    - I sorted BOTH the Demand tab (by Col A), and the Labor tab (by Col A & B)
    - I created a Dynamic Named Range {DNR} entitled _Demand
    - I added formulae on Labor tab (Cols D onwards) that leverage binary search approach {quicker to do 2 binary searches on big data than one linear lookup}

    On my machine, which is pretty old, the workbook calculates in half a dozen seconds -- still not brilliant, but decent enough I would say?
    {given the nature of calc engine the SUMIF calcs on first tab will run quickly, despite entire column range - i.e. overhead of introducing a DNR not worthwhile}

    NOTE: to reduce file size I removed the formulae from both Final & Labor tabs row 3 onwards -- to reassert copy row 2 and apply to remaining rows.
    Attached Files Attached Files

  16. #16
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Multiple area LOOKUP + SUM value

    @kimgag, suffice to say my prior suggestion didn't sit comfortably with me (given performance)

    I've attached an alternative version which should be quicker as, in hindsight, the introduction of the DNR referenced in my prior post / attachment is only going to slow things down.

    Per the prior example I would still advise sorting the 'Demand per material tab' data (by Col A) but you needn't sort the Labor tab.

    As before, to reduce file size, I have removed the formulae from rows 3 onwards in both Final & Labor tabs, to reinstate copy calcs in row 2 downwards.

    With all formulae in place the file should calculate in a second or so.

    edit: I also note, belatedly, that my previous suggestion included some rogue values {more specifically August & September}, apologies for any confusion caused.
    Attached Files Attached Files
    Last edited by XLent; 11-10-2018 at 04:01 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Lookup area to get value from value
    By jitendras in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-21-2017, 03:11 AM
  2. Replies: 2
    Last Post: 08-04-2016, 06:20 PM
  3. Replies: 2
    Last Post: 11-02-2015, 07:57 PM
  4. Lookup in an "area" of multiple columns/rows
    By Cantora in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-15-2013, 09:17 PM
  5. Excel 2007 : Lookup:all area codes
    By hdlqepm in forum Excel General
    Replies: 2
    Last Post: 12-16-2009, 06:28 PM
  6. Area code lookup
    By nms2130 in forum Excel General
    Replies: 1
    Last Post: 08-04-2008, 05:39 PM
  7. Using index to lookup over large area
    By liamthebof in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-28-2007, 09:11 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1