index match where data is in two Google Sheet tabs-CodePudding

I have a Google Sheet containing 2 tabs; tab1 and tab2

tab1 contents:

      A        B       C        D       E       F       G
 1  Group    Score    Pete     Bob    John    Sarah   David  
 2  A                 TRUE            TRUE
 3  B                         TRUE    TRUE             TRUE
 4  C                                 TRUE     TRUE

tab2 contents:

       A         B
   1 Pete       18
   2 John       25
   3 David       5
   4 Sarah       5
   5 Bob         8

*The two columns in tab2 have named ranges; each_name and name_points

I am trying to use INDEX MATCH to get the Score sum, which is the total of points allocated to each person as shown in tab2. However, the sum in the Score column in tab1 should only add points of persons with TRUE in their column

The desired output would therefore be:

      A        B       C        D       E       F       G
 1  Group    Score    Pete     Bob    John    Sarah   David  
 2  A           43    TRUE            TRUE
 3  B           38            TRUE    TRUE             TRUE
 4  C           13                    TRUE     TRUE

However, the formula that I've built so far, only produces 0

B2 = SUMIF(name_points,each_name,INDEX(C$1:G$1,0,MATCH(TRUE,C$2:G$2,0)))

B3 = SUMIF(name_points,each_name,INDEX(C$1:G$1,0,MATCH(TRUE,C$3:G$3,0)))

B4 = SUMIF(name_points,each_name,INDEX(C$1:G$1,0,MATCH(TRUE,C$4:G$4,0)))

CodePudding user response：

Try, in B2 of Sheet1

=sum(arrayformula(vlookup(query(transpose({$C$1:$G$1;C2:G2}),"select Col1 where Col2=TRUE",0),Sheet2!A:B,2,0)))