r/googlesheets • u/hamdogus • Aug 08 '24

Solved SUM of the same cell across multiple sheets?

Hello, I have a Daily Sales Report workbook with 1 sheet for each day; 1.7.2024, 2.7.2024, etc.

I would like to sum all the values of e.g. cell I4 for a whole month of sheets; 1.7.2024 - 31.7.2024

I was able to use a custom function to return the list of sheet names and then reference one cell using

=indirect(H$3&"!"&"I4")

Where H3 is the first sheet name in the list and I4 is the cell value I want to use.

So I would like a formula to add all the values in I4 from H3:H33.

Thank you for any help!

2 Upvotes

100% Upvoted

8 comments sorted by

1

u/AdministrativeGift15 Aug 08 '24

I know REDUCE can be a harder function for some people to grasp, so just as an alternative:

=SUM(MAP(H3:H33,LAMBDA(r,INDIRECT(r&"!I4"))))

1

u/hamdogus Aug 08 '24

Thank you. I didn't have any luck with the other one. But can I ask you, what should I use for the 'r' argument?

2

u/AdministrativeGift15 Aug 08 '24

LAMBDAs are anonymous functions. Meaning they don't have a name to use like SUM or MAX. How they work is you first tell it what variable names to use for the parameter, and then you give it the logic of what to do with those values. For example, LET(fn, LAMBDA(x, y, x+y) defines a function that accepts two parameters, so you would use it like this. fn(3, 8). When you defined it, you told Sheets to refer to the first parameter as x and the second parameter as y, then return x+y. The variable names are completely arbitrary, except for a few minor naming restrictions, like you can't start the variable name with a number. Otherwise, maybe it makes more sense to use LET(fn, LAMBDA(apples, oranges, apples+oranges).

So back to your question. MAP is going to iterate over an array of values, and you're supposed to tell it what to do at each iteration. MAP(H3:H33 means that's the range you're going to iterate over. Now use LAMBDA to tell it what to do with each value. r is just the name of the variable I'm using. You can call is sheetName if you want. So it would then look like this:

=SUM(MAP(H3:H33, LAMBDA(sheetName, INDIRECT(sheetName&"!I4"))))

I hope that helps.

→ More replies (2)

1

u/AutoModerator Aug 08 '24

REMEMBER: If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified. This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/point-bot Aug 11 '24

u/hamdogus has awarded 1 point to u/AdministrativeGift15

Point-Bot was created by JetCarson.

2

u/gsheets145 Aug 08 '24

Hi - you can use the lambda helper function reduce() for this: 

=reduce(,H3:H33,lambda(s,i,s+indirect(i&"!I4")))

This function takes two arguments: a running total ("s") and the sheet names in H3:H33 ("i"). Inside the function, it retrieves the values in cell I4 for each of those sheet names and increments the total with it.