0

I am trying to sum multiple columns from a table on a separate sheet using vlookup and matching the column index number with a name instead of the column number. I can return a single result but can only seem to sum the product of the columns using the array {3,4,5} etc.

In one tab I have my data (simplified for this example)

Data

In another sheet I want to show the totals for Fred and Bob and Betty and Jane for each category.

Result Table

This returns a single result:

=IFERROR(VLOOKUP(($A2&""),'P&L by job'!$A$2:$E$4,MATCH($A$2,'P&L by job'!$A$1:$E$1,0),FALSE),0)

This formula will result in the sumproduct:

=IFERROR(SUMPRODUCT(VLOOKUP(($A2&""),'P&L by job'!$A$2:$E$4{2,3},FALSE)),0)

The issue is when an additional column is added, or the columns are in a different order each time I import the data across, I need to change the column index number in my spreadsheet.

Is there a better approach?

1

Try peeling out Bob, Fred, Betty and Jane from the column headers.

=SUM(INDEX('P&L by job'!$A:$Z, MATCH($A2, 'P&L by job'!$A:$A, 0), MATCH(REPLACE(B$1, FIND(" and ", B$1), LEN(B$1), TEXT(,)), 'P&L by job'!$1:$1, 0)),
     INDEX('P&L by job'!$A:$Z, MATCH($A2, 'P&L by job'!$A:$A, 0), MATCH(REPLACE(B$1, 1, FIND(" and ", B$1)+4, TEXT(,)), 'P&L by job'!$1:$1, 0)))

enter image description here

  • It's working +1 ☺ – Rajesh S Apr 2 '20 at 10:28
  • Thanks Jeeped - I will try this – Wendy Apr 5 '20 at 22:53
0

Lookup formula wrapped with SUMPRODUCT and nested IF solves the issue:

Caveat:

Below shown method is best suitable to small data range like 8 to 10 columns, otherwise method/formula suggested by @Jeeped is better & a smart move.

enter image description here

How it works:

  • Insert some helper data in Row 1 (S1:X1), you may extend them, if and when required.
  • Write combination of names in Row 9 (S9:U9), remember you may change the order as your need.
  • Formula in S10:

     =IFERROR(IF(S$1="A",SUMPRODUCT(VLOOKUP($R10,$R$3:$X$5,{6,7},FALSE)),IF(S$1="B",SUMPRODUCT(VLOOKUP($R10,$R$3:$X$5,{4,5},FALSE)),IF(S$1="C",SUMPRODUCT(VLOOKUP($R10,$R$3:$X$5,{2,3},FALSE)),""))),"")
    

N.B.

  • Fill formula across the range.
  • You may adjust cell references & order of columns as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.