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

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.

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.

Originally Posted by dflak

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

We'll just agree to disagree on this point.

Not agreeing or disagreeing, just stating a different opinion