How to add cells from different pages to a cell

Question

I have a spreadsheet with 31 pages that keeps track of my bowling teams scores, points won, etc. Each week (30 weeks total) is a separate page. I have a summary page in that spreadsheet that keeps track of points won for each bowler from the 30 weeks bowled. Not every bowler bowls every week. The pages are labeled Week1, Week2, Week3,....,Week30 with a final page called SUMMARY.

On the weekly pages there is a table with the bowlers name in A35:A38 with the total points won per bowler in F35:F38.

I want a cell in SUMMARY that inspects each weekly page in cells A35:A38 for a bowler name (Bob, John, Rob, Steve, Mike, Jim, Rick) and if found, add the points in the corresponding row in F35:F38 to the SUMMARY cell.

Only 4 bowlers bowl each week but any 4 of the 7 on the team could be bowling. Right now I am updating the Summary cell manually after each week with the correct cell references for a particular bowler but I want to automate it.

Here is an example of what I might have for each bowler:

=SUM(Week1!F35,Week2!F36,Week3!F38,Week4!f35,Week7!f36)

How can I accomplish this. Thanks.

I think you should start by using tables for each week. You can then use formulas or power query to search all the tables. google.com/… — FJones
– FJones, Commented Nov 27 at 0:38
Add a "Week" column to your data and put everything on one sheet, preferably in a Table. Now you have everything in one table and easily accessible,. — Frank Ball
– Frank Ball, Commented Dec 1 at 20:30

VBasic2008 · Accepted Answer · 2025-11-27 09:38:21Z

2

Lookup Data Across Sheets Using 3D References

Score (B4)

=LET(dl,A4:A11,sl,TOCOL(Week1:Week30!A35:A38),sr,TOCOL(Week1:Week30!F35:F38),
    BYROW(dl,LAMBDA(dl,SUM(FILTER(sr,sl=dl,0)))))

Both (D4)

=LET(sl,TOCOL(Week1:Week30!A35:A38),sr,TOCOL(Week1:Week30!F35:F38),
    dl,UNIQUE(sl),
    HSTACK(dl,BYROW(dl,LAMBDA(dl,SUM(FILTER(sr,sl=dl))))))

Sorted (G4)

=LET(sl,TOCOL(Week1:Week30!A35:A38),sr,TOCOL(Week1:Week30!F35:F38),
    dl,UNIQUE(sl),
    SORT(HSTACK(dl,BYROW(dl,LAMBDA(dl,SUM(FILTER(sr,sl=dl))))),2,-1))

You could do the same using GROUPBY:

=LET(sl,TOCOL(Week1:Week30!A35:A38),sr,TOCOL(Week1:Week30!F35:F38),
   GROUPBY(sl,sr,SUM,,0,-2))

... or even:

=GROUPBY(TOCOL(Week1:Week30!A35:A38),TOCOL(Week1:Week30!F35:F38),SUM,,0,-2)

Ingredients (K4,L4,N4)

=TOCOL(Week1:Week30!A35:A38)
=TOCOL(Week1:Week30!F35:F38)
=UNIQUE(TOCOL(Week1:Week30!A35:A38))

edited Nov 27 at 9:38

answered Nov 27 at 9:25

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ron Rosenfeld Dec 1 at 1:39

And if you want to return multiple aggregations: =GROUPBY(TOCOL('Week 30:Week 1'!A35:A38), TOCOL('Week 30:Week 1'!F35:F38), HSTACK(SUM, AVERAGE, MAX, MIN)). By the way, why TOCOL vs VSTACK in this application?

Darren Bartrup-Cook · Accepted Answer · 2025-11-27 09:08:28Z

1

To dynamically add scores of each bowler from different worksheets VSTACK() would be a good choice. VSTACK() can stack data from multiple sheets at a time even from newly created sheets. If your case, I assume after stacking data you will need GROUPBY() function. First use =VSTACK('*'!A35:F100) which capture all worksheets data except summery sheet. It will turn like VSTACK(Week1:Week3!A35:F100) automatically if you have 3 sheets. So, for 30 sheets it will turn into VSTACK(Week1:Week30!A35:F100). Then apply GROUPBY() function to sum up points for each bowler. Final formula-

=LET(arr,VSTACK(Week1:Week3!A35:F100),
plr,CHOOSECOLS(arr,1),
scr,CHOOSECOLS(arr,6),
GROUPBY(plr,scr,SUM,,,,plr<>""))

edited Nov 27 at 9:08

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

answered Nov 27 at 2:24

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

1 Comment

Harun24hr Nov 27 at 2:32

Little bit shorter formula =LET(arr,VSTACK(Week1:Week3!A35:F100),fn,CHOOSECOLS,GROUPBY(fn(arr,1),fn(arr,6),SUM,,,,fn(arr,1)<>"")).

smallpepperz · Accepted Answer · 2025-11-27 02:30:10Z

I'm leaving this answer up in case you (or any one else) gets some use out of it, but it appears I wrote this thinking you meant google sheets, not Excel.

One way you could do this is using INDIRECT() and array formulas to count up the scores. I came up with the following formula, which assumes the bowler names are in the range A2:A8. The copy-able version is below, and I have an annotated version at the bottom.

=map(A2:A8, lambda(bowler, sum(map(sequence(50,1),lambda(week, iferror(XLOOKUP(bowler,INDIRECT("Week"&week&"!A35:A38"),INDIRECT("Week"&week&"!F35:F38"),0)))))))

=MAP(
    A2:A8, // Bowler name range
    LAMBDA( // For each bowler...
        bowler,
        SUM( // Sum the result of...
            MAP( // Iterate over WeekN sheets where N is 1-50 and find the bowler's score
                SEQUENCE(
                    50, // If you need more than 50 weeks, simply increase this value
                    1
                ),
                LAMBDA( // For each week, get the bowler's score
                    week,
                    IFERROR(
                        XLOOKUP(
                            bowler, // Search key (bowler's name)
                            INDIRECT(
                                "Week"&week&"!A35:A38" // Name range
                            ),
                            INDIRECT(
                                "Week"&week&"!F35:F38" // Score range
                            ), 
                            0 // Default to zero if not found
                        )
                    )
                )
            )
        )
    )
)

To see this in action, check out my Example Sheet

Collectives™ on Stack Overflow

How to add cells from different pages to a cell

3 Answers 3

Lookup Data Across Sheets Using 3D References

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Lookup Data Across Sheets Using 3D References

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related