Sum Worksheets

[Neil Pearce]

Having searched and read similar queries regarding summing there was a
reference to the website: http://www.mcgimpsey.com/excel/threedsumif.html

Adapting the formula provided and (with great originality) naming my
worksheets to sum, "worksheets_to_sum", the following formula works great.

=SUMPRODUCT(SUMIF(INDIRECT("'"&Worksheets_To_Sum&"'!A14:A23"),$A19,INDIRECT("'"&Tabs&"'!B14:B23")))

However I wish to copy and paste the formula to several other columns so
that columns C onwards can be summed. In its current form the sum range
remains static at B14:B23.

I attempted to correct this by adding a header row that would indicate the
column to be summed, e.g. Cell D17

=SUM(...INDIRECT(" ' "&Tabs&" ' ! & D17 & "14:" & D17 & "23")))

This did not work. Any ideas?


Thanking-you,

Neil

 

[Rodrick]

Hi,

I tried the formula which you mentioned, and it did work for me.

=SUMPRODUCT(SUMIF(INDIRECT("Worksheets_To_Sum!A14:A23"),$A19,INDIRECT("Tabs!"&D17&"14:"&D17&"23")))

I hope you have the column name i.e. if you want to sum column "E" then cell
D17 reflects "E" and not 5.

Check if there is some problem with your data.

 

[Neil Pearce]

Sorry for the typo. Should have been...

=SUMPRODUCT(SUMIF(INDIRECT("'"&Worksheets_To_Sum&"'!A14:A23"),$A19,INDIRECT("'"&Worksheets_To_Sum&"'!B14:B23")))

 

[Rodrick]

are you getting an error message or is it just summing to zero?

If you are getting an error message, could you please let me know.

 

[Neil Pearce]

Hi Rodrick,

Thanks for helping me out here. I get a #REF! error message.

=SUMPRODUCT(SUMIF(INDIRECT("'"&Worksheets_To_Sum&"'!A2:A11"),$A3,INDIRECT("'"&Worksheets_To_Sum&"'!B2:B11")))

Works fine.

I just amend the forumla such that the B2:B11 instead references a cell that
dictates the column to sum.

 

[Rodrick]

could you confirm that you have the valid tab name in the cell which you have
named as "Worksheet_To_Sum"?

You are getting this error message because formula is not able to get the
valid sheet name in the workbook which you have mentioned in the
"Worksheet_To_Sum" cell.

 

[Neil Pearce]

Starting from the basics.

3 worksheets titled 1,2 & 3.
All worksheets have 10 in cell A1

4th worksheet titled Summary
Cell A1 = 1
Cell A2 = 2
Cell A3 = 3

Highlight Cells A1:A3 on Summary and define them as the name, Tabs.

Cell A5, enter =SUM(1:3!A1)
Cell A5 correctly displays 30

Cell A6, enter =SUM(INDIRECT(" ' "&Tabs&" ' ! A1 ")
Cell A6 displays #VALUE ERROR!

I'm missing something fairly fundamental and pretty obvious it would appear!

 

[Rodrick]

If your workbook just have 4 sheets including the Summary sheet, then you can
use the below mentioned formula to do the calculation:

=SUM(IF(A1<>"",INDIRECT("'"&A1&"'!A1"),0),IF(A2<>"",INDIRECT("'"&A2&"'!A1"),0),IF(A3<>"",INDIRECT("'"&A3&"'!A1"),0))

Summing up mutliple sheets wont work without using ":" in between.