Sum column in multiple worksheets

[Yorelsemaj]

Assistance please!

I know there is a way to sum a cell value in the same address in different worksheets: =SUM(Sheet1:Sheet3!B2).

This is assuming the items are aligned exactly in Sheet1, Sheet2 & Sheet3 as outlined below:
ColA ColB
Item Qty
1001 10
1002 50
1003 35
1004 25

Formula: =SUM(Sheet1:Sheet3!B2); Result: 30

What if however, in Sheet3 the sort order was different for ColA:
ColA ColB
Item Qty
1004 25
1003 35
1002 50
1001 10

How would I achieve the same result?

 

[T. Valko]

Assistance please!

I know there is a way to sum a cell value in the same address in different worksheets: =SUM(Sheet1:Sheet3!B2).

This is assuming the items are aligned exactly in Sheet1, Sheet2 & Sheet3 as outlined below:
ColA ColB
Item Qty
1001 10
1002 50
1003 35
1004 25

Formula: =SUM(Sheet1:Sheet3!B2); Result: 30

What if however, in Sheet3 the sort order was different for ColA:
ColA ColB
Item Qty
1004 25
1003 35
1002 50
1001 10

How would I achieve the same result?

Sounds like you want to do a SUMIF across sheets.

What are your REAL sheet names?

 

[Aladin Akyurek]

Assistance please!

I know there is a way to sum a cell value in the same address in different worksheets: =SUM(Sheet1:Sheet3!B2).

This is assuming the items are aligned exactly in Sheet1, Sheet2 & Sheet3 as outlined below:
ColA ColB
Item Qty
1001 10
1002 50
1003 35
1004 25

Formula: =SUM(Sheet1:Sheet3!B2); Result: 30

What if however, in Sheet3 the sort order was different for ColA:
ColA ColB
Item Qty
1004 25
1003 35
1002 50
1001 10

How would I achieve the same result?

See:

http://www.mrexcel.com/forum/showthread.php?t=119020

 

[T. Valko]

Sounds like you want to do a SUMIF across sheets.

What are your REAL sheet names?

If you only have those 2 sheets then you can do this...

On your summary sheet:

Summary

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Item</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Qty</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1001</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">20</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1002</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">100</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1003</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">70</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1004</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">50</TD></TR></TBODY></TABLE>


Formula entered in B2 and copied down:

=SUMIF(Sheet1!A$2:A$5,A2,Sheet1!B$2:B$5)+SUMIF(Sheet2!A$2:A$5,A2,Sheet2!B$2:B$5)

If you have many sheets then you may not want to use that method. Post back with the sheet names if that's the case.

 

[Yorelsemaj]

Thanks for the responses thus far.

I had only used two Sheets in the example but I have approximately 76 sheets are named as follows: RT001, RT002, RT003, RT004, RT005...........RT076

The example provided by Valko would only be effective if I had a small amount of sheets working with.

Any other suggestions?

 

[T. Valko]

Thanks for the responses thus far.

I had only used two Sheets in the example but I have approximately 76 sheets are named as follows: RT001, RT002, RT003, RT004, RT005...........RT076

The example provided by Valko would only be effective if I had a small amount of sheets working with.

Any other suggestions?

Try this...

Let's assume the range of interest is A2:B10 on each sheet.

Create this named formula.

In Excel 2003 and earlier goto the menu Insert>Name>Define

In Excel 2007 and later goto the Formulas tab>Defined Names>Define Name

• Name: Sheets
• Refers to: =TEXT(ROW(INDIRECT("1:76")),"000")
• OK

On your summary sheet enter this formula in cell B2:

=SUMPRODUCT(SUMIF(INDIRECT("'RT"&Sheets&"'!A2:A10"),A2,INDIRECT("'RT"&Sheets&"'!B2:B10")))

 

[Yorelsemaj]

Valko.

The solution you provided works very well. Thanks.

I must point out to you though, having provided the sheet names as RT001...RT076; using the formula provided works as long as there is no break in the sheet name sequence. For example, if RT005 was not used and was skipped, the formula returns a #REF error. While this may not happen, I still decided to test the formula for the possibilities that would result in incorrect result or error.

Why do you think that happened?

 

[Aladin Akyurek]

Valko.

The solution you provided works very well. Thanks.

I must point out to you though, having provided the sheet names as RT001...RT076; using the formula provided works as long as there is no break in the sheet name sequence. For example, if RT005 was not used and was skipped, the formula returns a #REF error. While this may not happen, I still decided to test the formula for the possibilities that would result in incorrect result or error.

Why do you think that happened?

A non-existing sheet name will cause that error.

The link I provided contains code posted by Jindon. That codes allows you to gather the relevant sheet names into a range. You can use that range as the sheet list...

 

[T. Valko]

Valko.

The solution you provided works very well. Thanks.

You're welcome. Thanks for the feedback!

I must point out to you though, having provided the sheet names as RT001...RT076; using the formula provided works as long as there is no break in the sheet name sequence. For example, if RT005 was not used and was skipped, the formula returns a #REF error. While this may not happen, I still decided to test the formula for the possibilities that would result in incorrect result or error.

Why do you think that happened?

If a sheet that is defined in the sequence:

=TEXT(ROW(INDIRECT("1:76")),"000")

Doesn't exist, then you'll ge the #REF! error. #REF! means an invalid reference error. Since a sheet may not exist this generates an invalid reference and thus, an error.

A way to account for that is to list the sheets to be included in the cacluation in a range of cells. Since you had a lot of sheets I figured this was not something you wanted to do.

Here's a link that describes a method of listing the sheet names in a range of cells:

http://www.mrexcel.com/forum/showthread.php?p=2118912#post2118912

You would then give this range a defined name like Sheets.

Then the formula would become:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!A2:A10"),A2,INDIRECT("'"&Sheets&"'!B2:B10")))

You might have to "tweak" the sheet name list as the method described above will list EVERY sheet in the workbook. You may not want to include EVERY sheet in the calculation.

 

[Yorelsemaj]

Valko.

You have been more help than I anticipated. Thank you for sharing your knowledge and expertise. Keep us the good work my friend.

Regards.