Automatically sum the same cell across multiple sheets in Excel, including new sheets

Question

Hello, I have such a case.
I need to know how scratch automatic sum of a given cell from all sheets.
I mean, if I already have Sheet1, Sheet2, Sheet3 and I have one cell totaled in another sheet, and I will add another sheet to keep it totaling there?

walek33 · Answer

Rather, nothing happens in nature. If you want to automate certain activities, use a macro.

meriadook · Answer

It is enough to put e.g. the formula = sum (a: b! A1), where a and b are the names of the sheets limiting the ones you want to sum. You can name them whatever you want.

Sheets in turn:
a, Sheet1, Sheet2, ...... SheetN, b

This formula sums cells A1 in worksheets a to b inclusive, if you now insert some extra worksheet between a and b, it will also be summed.

adamas_nt · Answer

Rather, this will result in an argument error ... As a colleague mentioned fight33 there remains a macro or user function using a loop Eg Code: text Expand Select all Copy to clipboardFor i = 1 To Sheets.Count In the case of user functions, Excel does not recalculate the worksheet automatically, but they can be forced eg at an event Worksheet_Activate .

meriadook · Answer

and have you tried my method at least? because I can see that it is not;] and it is simpler than macros

adamas_nt · Answer

meriadook Indeed. I tested it and it works. You forgot to add that the names do not matter, but the key is to arrange the tabs on the sheet bar. I return my honor and congratulate you on your knowledge.

leszekkpl · Answer

Hello
And is it possible to sum sheets in Excel, but the sheets would be in different Excel files and they would also have different names.
For example, I have xls files called data1.xls in it 100 sheets, another file called data2.xls in it 100 sheets, is it possible to sum up all these sheets in a separate file in excel (200 in total)?
These worksheets are named with numbers from 1 to 100.
I will add that the location of these files is:
C: \ Documents and Settings \ office \ Pulpit \ data1.xls
C: \ Documents and Settings \ office \ Pulpit \ dane2.xls
Thank you in advance for your answer.

walek33 · Answer

If there are only two files, it is enough to modify the colleague's formula meriadook . Code: text Expand Select all Copy to clipboard=SUMA([Zeszyt1]Arkusz1:Arkusz3!A1;[Zeszyt2]Arkusz1:Arkusz3!A1)

leszekkpl · Answer

and anyone already used such a formula, sum between two excel files?

Added after 29 [minutes]:

I managed to do it if someone needed, then below is this formula:
= SUM ('C: \ Documents and Settings \ office \ Pulpit \ [dane1.xlsx] 1'! A1) + SUM ('C: \ Documents and Settings \ office \ Pulpit \ [dane2.xlsx] 1'! A1)
it sums up the first sheets in two Excel files, with one A1 cell, but the range can be modified as needed.
It is also known that everyone can have a different location, different file and sheet names.

walek33 · Answer

leszekkpl And why in your example SUM() ? You will achieve the same by: Code: text Expand Select all Copy to clipboard='C:\Documents and Settings\biuro\Pulpit\[dane1.xlsx]1'!A1+'C:\Documents and Settings\biuro\Pulpit\[dane2.xlsx]1'!A1

Automatically sum the same cell across multiple sheets in Excel, including new sheets

Post #1

Post #2

Post #3

Post #4

Post #5

Post #6

Post #7

Post #8

Post #9

Post #10

Didn't find an answer? Ask Artificial Intelligence

Topic summary