+ Reply to Thread
Results 1 to 7 of 7

Replace multiple SumProduct/Vlookup with a dynamic array of conditions

  1. #1
    Registered User
    Join Date
    03-17-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    9

    Replace multiple SumProduct/Vlookup with a dynamic array of conditions

    Hi guys,

    I have tried to see if there was a similar post to my issue but are not able to find something that match what I am trying to do.

    I have been assigned to come up with a roster and I'd love to help the one from someone that feels comfortable with it. I am working for an NGO so resources are quite limited, thus me an HR person doing the roster for medical people

    Anyway I'd like to minimize a formula I am using to calculate the total number of shift in my roster for every person that work.
    I am using a mix of SumProduct and Vlookup in the formula to calculate the total. The idea behind it is to make it as simple as possible, which is far to be the case now.

    Below my two tables:

    TABLE 1 > A1:F3
    Total
    Bob D1 D2 D2 D2 7
    Kyle D3 D3 D1 D1 8

    TABLE 2 > A5:B7
    D1 1
    D2 2
    D3 3


    Formula in F7:
    =SUMPRODUCT(--(B2:E2="D1")*VLOOKUP("D1",$A$5:$B$7,2,FALSE))+SUMPRODUCT(--(B2:E2="D2")
    *VLOOKUP("D2",$A$5:$B$7,2,FALSE))+SUMPRODUCT(--(B2:E2="D3")*VLOOKUP("D3",$A$5:$B$7,2,FALSE))

    Problem
    As you can see the formula is already quite long and I must admit pretty messy. Problem is if my table becomes 15 rows I'll have to add everything manually.

    Would it be possible to have all conditions dynamic so that I don't need to enter any condition manually as it is currently the case, e.g. "D1", "D2", "D3"?

    Not sure if that makes sense to any of you but would definitely appreciate some wisdom.

    Thanks a lot,
    Fab
    Attached Files Attached Files
    Last edited by Faboolous; 03-17-2016 at 11:18 AM.

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Replace multiple SumProduct/Vlookup with a dynamic array of conditions

    Try putting the formula below in F2 using Ctrl + Shift + Enter to confirm it. Fill down as necessary.

    =SUM(LOOKUP($B2:$E2,$A$5:$A$7,$B$5:$B$7))

    Edit: At some point, you'll want to get table 2 out of table 1's way, I imagine. Just change the A5:A7 and B5:B7 portions of the formula to conform to wherever you relocate table 2. Beyond that, you shouldn't have to make adjustments for every new entry in table 1.
    Last edited by CAntosh; 03-17-2016 at 12:13 PM.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,146

    Re: Replace multiple SumProduct/Vlookup with a dynamic array of conditions

    I agree with cantosh. Get Table 2 out of the way. This is what I did. I moved it to Sheet 2

    I made it an Excel Table with the name Table_Lookup. There are several advantages to using tables. One of them is that they use the table name in the column headers instead of cell references, so the formula becomes: =Vlookup(A2,Table_Lookup,2,false) instead of =Vlookup(A2,Sheet2!$A$2:$B$4). Another advantage is the table knows how big it is so you don't have to guess at the range or change the formula when you add data. If you add a new D value to Cell A5 on sheet 2, it becomes part of the table.

    This makes the formula cantosh provided =SUM(LOOKUP(B2:E2,Table_Lookup)).

    I also put the data on Sheet 1 into an Excel table. Another advantage is that when you add a row to a table, the formulas get copied down automatically.

    Finally, just for fun, I put data validation on the cells to look up only valid data. =INDIRECT("Table_Lookup[Value]") Oh yeah, that's yet another advantage of Excel Tables, they are automatic dynamic ranges that can be used for lookup. And yes, when you add a row to a table, the validations get copied down too.

    This article should get you started with Excel Tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  4. #4
    Registered User
    Join Date
    03-17-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    9

    Thumbs up Re: Replace multiple SumProduct/Vlookup with a dynamic array of conditions

    @Cantosh @dflak

    Wow you guys rock Thanks a lot that is exactly what I was looking for. I used both of your technics and it works like a charm.
    I am going to put all generic table in one sheet and make good use of the Table into Excel (simple thing I was not even aware of).
    Just assumed since I was in Excel I was already creating a table.

    Anyway, thank you so much for your help. I'm already on week-end here in Bangladesh but could not wait to test your answers out.

    Have a great weekend,
    Fab

    PS: Array formula + Tables = Happy Man!

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,250

    Re: Replace multiple SumProduct/Vlookup with a dynamic array of conditions

    Quote Originally Posted by dflak View Post
    There are several advantages to using tables. One of them is that they use the table name in the column headers instead of cell references, so the formula becomes: =Vlookup(A2,Table_Lookup,2,false) instead of =Vlookup(A2,Sheet2!$A$2:$B$4).
    Not everyone would agree that structured table nomenclature is a good thing. If I just have you the formula =Vlookup(A2,Table_Lookup,2,false) with no table to reference, you would probably have a hard time picturing what it was doing, but a formula like =Vlookup(A2,Sheet2!$A$2:$B$4) is easy to read, and you can immediately interpret what it is doing.

    I, for 1, am not a big fan of how formulas are represented from structured tables.

    edit: dont get me wrong, they do have some serious advantages of a range, they are a tool, just like any other function, and have their place in any tool box
    Last edited by FDibbins; 03-18-2016 at 02:49 AM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,146

    Re: Replace multiple SumProduct/Vlookup with a dynamic array of conditions

    We'll just agree to disagree on this point.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,250

    Re: Replace multiple SumProduct/Vlookup with a dynamic array of conditions

    Not agreeing or disagreeing, just stating a different opinion

+ 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. [SOLVED] Replace multiple SumProduct/Vlookup with a dynamic array of conditions
    By Faboolous in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2016, 11:12 AM
  2. Replies: 2
    Last Post: 08-21-2015, 03:50 AM
  3. [SOLVED] Dynamic Count and Sum Array Formulas Based on Multiple Conditions
    By 5150 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-27-2014, 04:35 PM
  4. [SOLVED] Make print area dynamic & replace a SUMPRODUCT with code!
    By apla in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-20-2013, 10:04 AM
  5. Replies: 13
    Last Post: 12-13-2012, 11:44 AM
  6. Replies: 3
    Last Post: 01-07-2012, 02:51 AM
  7. Sumproduct with dynamic array
    By benaw in forum Excel General
    Replies: 3
    Last Post: 10-18-2009, 07:24 PM

Tags for this Thread

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