Sum sheets based on criteria
 

Hello, I have a workbook containing 30+ worksheets. For simplicity, let's say
I have 30 sheets with data and a sheet with formulas that displays totals.
The setup and columns are all the same for the 30 sheets. The names of the
sheets start at 1 and go to 30.

In the total sheet, I would like to be able to type in a range, say 1 to 7,
and have the totals from only those sheets calculated. The totals will be the
sum of the whole column. So, it would be the sum total of column E for sheets
1 through 7.

I am thinking about entering the range and then clicking a command button
that would do the work. Any ideas?

Sum sheets based on criteria
 

If?? you have a total in each sheet in the SAME cell then
=sum(sheet1:sheet7!a3)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"npop03" wrote in message
...
Hello, I have a workbook containing 30+ worksheets. For simplicity, let's
say
I have 30 sheets with data and a sheet with formulas that displays totals.
The setup and columns are all the same for the 30 sheets. The names of the
sheets start at 1 and go to 30.

In the total sheet, I would like to be able to type in a range, say 1 to
7,
and have the totals from only those sheets calculated. The totals will be
the
sum of the whole column. So, it would be the sum total of column E for
sheets
1 through 7.

I am thinking about entering the range and then clicking a command button
that would do the work. Any ideas?

Sum sheets based on criteria
 

Correct, and that's what I am doing for the total of all sheets, but I am
wondering if there's a way to type "sheet 1" in a cell, "sheet 7" in another
cell and then have your formula below change accordingly.

I am making this for non-experienced Excel users and I'd like to make it so
they don't have to change the formula each time (otherwise your solution is
what I would do)

Let me know if this helps clarify things and if it's even possible, thanks!

"Don Guillett" wrote:

If?? you have a total in each sheet in the SAME cell then
=sum(sheet1:sheet7!a3)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"npop03" wrote in message
...
Hello, I have a workbook containing 30+ worksheets. For simplicity, let's
say
I have 30 sheets with data and a sheet with formulas that displays totals.
The setup and columns are all the same for the 30 sheets. The names of the
sheets start at 1 and go to 30.

In the total sheet, I would like to be able to type in a range, say 1 to
7,
and have the totals from only those sheets calculated. The totals will be
the
sum of the whole column. So, it would be the sum total of column E for
sheets
1 through 7.

I am thinking about entering the range and then clicking a command button
that would do the work. Any ideas?

Sum sheets based on criteria
 

No can do, even with indirect. You will have to have the sheets in order by
index.

Sub sumacrossshts()
fs = Sheets(CStr(Range("f3"))).Index
ls = Sheets(CStr(Range("f4"))).Index
For i = fs To ls
'MsgBox Sheets(i).Name
ms = ms + Sheets(i).Range("a3")
Next i
MsgBox ms
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"npop03" wrote in message
...
Correct, and that's what I am doing for the total of all sheets, but I am
wondering if there's a way to type "sheet 1" in a cell, "sheet 7" in
another
cell and then have your formula below change accordingly.

I am making this for non-experienced Excel users and I'd like to make it
so
they don't have to change the formula each time (otherwise your solution
is
what I would do)

Let me know if this helps clarify things and if it's even possible,
thanks!

"Don Guillett" wrote:

If?? you have a total in each sheet in the SAME cell then
=sum(sheet1:sheet7!a3)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"npop03" wrote in message
...
Hello, I have a workbook containing 30+ worksheets. For simplicity,
let's
say
I have 30 sheets with data and a sheet with formulas that displays
totals.
The setup and columns are all the same for the 30 sheets. The names of
the
sheets start at 1 and go to 30.

In the total sheet, I would like to be able to type in a range, say 1
to
7,
and have the totals from only those sheets calculated. The totals will
be
the
sum of the whole column. So, it would be the sum total of column E for
sheets
1 through 7.

I am thinking about entering the range and then clicking a command
button
that would do the work. Any ideas?

Sum sheets based on criteria
 

Don, I think your solution will work - I was wondering if we could modify 2
things:

1) Instead of a message box, I would like to place the value in a cell - say
J4...

2) Instead of summing only one cell for each sheet, is there a way we can
sum the entire column for each sheet? Specifically, instead of summing A3, I
would like to sum column K.

Please let me know and thanks for your patience and help!

"Don Guillett" wrote:

No can do, even with indirect. You will have to have the sheets in order by
index.

Sub sumacrossshts()
fs = Sheets(CStr(Range("f3"))).Index
ls = Sheets(CStr(Range("f4"))).Index
For i = fs To ls
'MsgBox Sheets(i).Name
ms = ms + Sheets(i).Range("a3")
Next i
MsgBox ms
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"npop03" wrote in message
...
Correct, and that's what I am doing for the total of all sheets, but I am
wondering if there's a way to type "sheet 1" in a cell, "sheet 7" in
another
cell and then have your formula below change accordingly.

I am making this for non-experienced Excel users and I'd like to make it
so
they don't have to change the formula each time (otherwise your solution
is
what I would do)

Let me know if this helps clarify things and if it's even possible,
thanks!

"Don Guillett" wrote:

If?? you have a total in each sheet in the SAME cell then
=sum(sheet1:sheet7!a3)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"npop03" wrote in message
...
Hello, I have a workbook containing 30+ worksheets. For simplicity,
let's
say
I have 30 sheets with data and a sheet with formulas that displays
totals.
The setup and columns are all the same for the 30 sheets. The names of
the
sheets start at 1 and go to 30.

In the total sheet, I would like to be able to type in a range, say 1
to
7,
and have the totals from only those sheets calculated. The totals will
be
the
sum of the whole column. So, it would be the sum total of column E for
sheets
1 through 7.

I am thinking about entering the range and then clicking a command
button
that would do the work. Any ideas?

Sum sheets based on criteria
 

Assumes you have a total in the same cell on each sheet, ie:a3
to put in a cell on the active sheet
'MsgBox ms
range("a3").value=ms

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"npop03" wrote in message
...
Don, I think your solution will work - I was wondering if we could modify
2
things:

1) Instead of a message box, I would like to place the value in a cell -
say
J4...

2) Instead of summing only one cell for each sheet, is there a way we can
sum the entire column for each sheet? Specifically, instead of summing A3,
I
would like to sum column K.

Please let me know and thanks for your patience and help!

"Don Guillett" wrote:

No can do, even with indirect. You will have to have the sheets in order
by
index.

Sub sumacrossshts()
fs = Sheets(CStr(Range("f3"))).Index
ls = Sheets(CStr(Range("f4"))).Index
For i = fs To ls
'MsgBox Sheets(i).Name
ms = ms + Sheets(i).Range("a3")
Next i
MsgBox ms
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"npop03" wrote in message
...
Correct, and that's what I am doing for the total of all sheets, but I
am
wondering if there's a way to type "sheet 1" in a cell, "sheet 7" in
another
cell and then have your formula below change accordingly.

I am making this for non-experienced Excel users and I'd like to make
it
so
they don't have to change the formula each time (otherwise your
solution
is
what I would do)

Let me know if this helps clarify things and if it's even possible,
thanks!

"Don Guillett" wrote:

If?? you have a total in each sheet in the SAME cell then
=sum(sheet1:sheet7!a3)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"npop03" wrote in message
...
Hello, I have a workbook containing 30+ worksheets. For simplicity,
let's
say
I have 30 sheets with data and a sheet with formulas that displays
totals.
The setup and columns are all the same for the 30 sheets. The names
of
the
sheets start at 1 and go to 30.

In the total sheet, I would like to be able to type in a range, say
1
to
7,
and have the totals from only those sheets calculated. The totals
will
be
the
sum of the whole column. So, it would be the sum total of column E
for
sheets
1 through 7.

I am thinking about entering the range and then clicking a command
button
that would do the work. Any ideas?