sum and vlookup

[mopey12345]

I have 5 named tables W1T,W2T,W3T,W4T,W5T each on a separate sheet which has a variable a list of names in column A . I need to add items from each occurrence of the same name from the 5 tables on a summary sheet. This formula works but can it be simplified, or would a VBA routine be better? Thanks in advance Phil =SUM(IFERROR(VLOOKUP($A2,W1T,6,FALSE),0),IFERROR(VLOOKUP($A2,W2T,6,FALSE),0),IFERROR(VLOOKUP($A2,W3T,6,FALSE),0),IFERROR(VLOOKUP($A2,W4T,6,FALSE),0),IFERROR(VLOOKUP($A2,W5T,6,FALSE),0))

 

[mopey12345]

Tried INDEX MATCH but same length of formula. =SUM(IFERROR(INDEX(W1T,MATCH($A2,M_WK1!$A$2:$A$220,0),E$230),0),IFERROR(INDEX(W2T,MATCH($A2,M_WK2!$A$2:$A$220,0),E$230),0),IFERROR(INDEX(W3T,MATCH($A2,M_WK3!$A$2:$A$220,0),E$230),0),IFERROR(INDEX(W4T,MATCH($A2,M_WK4!$A$2:$A$220,0),E$230),0),IFERROR(INDEX(W5T,MATCH($A2,M_WK5!$A$2:$A$220,0),E$230),0))

 

[jasonb75]

This formula works but can it be simplified

I assume that you mean 'shortened' as the current formula is simple, just a bit long.
If that is correct then I would say that there is nothing practical that you could do with it. If you are going to be adding more tables over time then maybe vba methods would be worth considering, but for what you have at present the existing formula is probably the most practical solution.

 

[GraH]

Hi there is a solution posted here SUMIF functionality in a 3D reference

I personally would go for a Power Query solution. You have 2010, it could be possible if you have the add-on installed.

 

[jasonb75]

Hi there is a solution posted here SUMIF functionality in a 3D reference

That doesn't simplify the formula though, it makes it shorter and less efficient.

 

[GraH]

Agreed. What's understood by simplify? Like you said in #3.
Simple for me is Power Query. No formulas.

 

[mopey12345]

I assume that you mean 'shortened' as the current formula is simple, just a bit long.
If that is correct then I would say that there is nothing practical that you could do with it. If you are going to be adding more tables over time then maybe vba methods would be worth considering, but for what you have at present the existing formula is probably the most practical solution.

Thanks, response much appreciated . 5 weeks of data will be the max so I'll stick with vlookup.

 

[mopey12345]

Hi there is a solution posted here SUMIF functionality in a 3D reference

I personally would go for a Power Query solution. You have 2010, it could be possible if you have the add-on installed.

Thanks for the response. Much appreciated.

 

[mopey12345]

I assume that you mean 'shortened' as the current formula is simple, just a bit long.
If that is correct then I would say that there is nothing practical that you could do with it. If you are going to be adding more tables over time then maybe vba methods would be worth considering, but for what you have at present the existing formula is probably the most practical solution.

Thanks. Well put. Just is very long.