• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum across multiple sheets

T

TrickY

New Member
Hello,


Hope anyone of you can help me with the following:


I want to make a sum of cell D10 in multiple sheets.


This formula I typed manually:

'=SUM(INDIRECT("'"&E4&"'!"&H5);INDIRECT("'"&E6&"'!"&H5))'


E4 = The name of a sheet

E5 = Blank cell (The formula returns "" - So not really empty)

E6 = Another sheetname

H5 = D10


If i also add E5 (the blank reference to a sheetname) in the formula it goes wrong.

The column "E" could go to E50 for example, it expands every time and has blank cells.


Does anyone know a formula to get this to work?

Or maybe a macro


Thanks in advance!


Greetings,


("E" is not the only column with sheetnames (sum between first and last sheet is not an option))
 
VBA for you

[pre]
Code: 
Sub SumD1()
Dim ws As Worksheet
Dim counter As Double

counter = 0

For Each ws In Application.Worksheets
counter = counter + ws.Cells(10, 4).Value
Next ws

MsgBox counter

End Sub
[/pre]
 
Sorry I posted a macro that will do all sheets. There are a couple of ways to do it.


Firstly you could have a 'marker' on the sheets you want to sum. Say for example each sheet you wish to sum has "Include" in cell A1 then:

[pre]
Code: 
Sub SumD1()
Dim ws As Worksheet
Dim counter As Double

counter = 0

For Each ws In Application.Worksheets
If ws.Cells(1,1).Value = "Include" Then
counter = counter + ws.Cells(10, 4).Value
End If
Next ws

MsgBox counter

End Sub
[/pre]
 
Or if you still want to loop down the Sheet names you have put in E4 adn below:

[pre]
Code: 
Sub SumD1()
Dim ws As Worksheet
Dim counter As Double
Dim Sheetname As String

counter = 0

Range("E4").Select

Do
Sheetname = ActiveCell.Value
counter = counter + Sheets(Sheetname).Cells(10, 4).Value
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell)

MsgBox counter

End Sub
[/pre]
 
Hello @TrickY,


You could also use a formula based approach to sum across multiple worksheets. (Your original formula was nearly there.)


For the following formula, I have assumed the following setup:

cell B1 contains the value "D10" (without quotes)

cell B2 contains the value "Sheet1" (again without quotes)

cell B3 contains the value "Sheet3"

cell B4 contains the value "Sheet5"

cell B5 contains the value "Sheet7"


(You will need to update the above values to reflect the names of your worksheets.)


Then the following formula, entered with Ctrl + Shift + Enter will provide you the required sum:

=SUM(N(INDIRECT(B2:B5 & "!" & B1)))


In this case, the sheet names do not have any spaces. If you do, you will need to wrap them in quotes in the formula.


(I am not sure I understood your comment about blank values in some cells. But if this does not work, feel free to let us know.)


Cheers,

Sajan.
 
Hi Sajan,


Thanks for your formula!

The formula works when B2,B3,B4,B5 are filled with sheetnames.


I have blank cells and ill try to explain: (i do not use the real names)


1) I extract the sheetnames in Column CC.


The sheetnames will come out like this:

CC1 = x -1

CC2 = x -2

CC3 = y -1

CC4 = x -3

CC5 = y -2


2) Then I put sheetnames that start with "x" in column AA and sheetnames that start with "y" in column BB.


It will look like this: (where "" is the result of a IF formula)


AA1 = x -1

AA2 = x -2

AA3 = ""

AA4 = x -3

AA5 = ""


BB1 = ""

BB2 = ""

BB3 = y -1

BB4 = ""

BB5 = y -2


{=SUM(N(INDIRECT(AA1:AA2 & "!" & B1)))} --- Works

{=SUM(N(INDIRECT(AA1:AA5 & "!" & B1)))} --- Will not work


So I need something to get the formula to work with some cells with "" in the range.

Or I need a way to make a range where there are no cells with "" in the range.
 
Hi,

The following formula should give you what you want:


=SUM(N(INDIRECT("'" & T(OFFSET(sheetnames, SMALL(IF(TRIM(sheetnames)<>"",ROW(sheetnames)),ROW($A$1:INDEX($A:$A,COUNTIF(sheetnames,"<>"))))-1, 0, 1,1)) & "'" & "!"&B1)))


enter with Ctrl + Shift + Enter


Here "sheetnames" refers to your data range (such as AA1:AA5) where you have your sheetnames.


Cheers,

Sajan.
 
I had to change the list of sheetnames a little bit.


Because the sheetname list is filled with a formula like this: IF(B1=C1;Sheetname;"")

The result "" in some cells lead to an error.

I replaced "" with "Last" (an empty sheet) so now it always has a sheetname to sum up.


The formula does eat alot of processing power, my excel runs wild with calculations
 
Hi,

Volatile functions (such as OFFSET, INDIRECT) can be resource-hungry if you have a lot of them in a given workbook. However, the good news is that you have options:

-- Structure your worksheets so that you can avoid or minimize use of the volatile functions

-- Use volatile functions only for a few calculations

-- As a last resort, you can opt to use better hardware


Feel free to post your workbook (after removing any sensitive data, of course)... folks on this forum would be happy to advise you regarding optimizations.


Cheers,

Sajan.
 
You must log in or register to reply here.
Back
Top