Need Function for adding rows on multiple sheets... struggling rookie ;) Thanks.

[Steve]

Hello,

I am trying to create a sheet which totals numbers from column B on
one final sheet. What we have is a bunch of machines which we want to
track the hours of on a monthly basis and on a "total use" basis. I
created a workbook with 13 sheets, one for each month and then a
"total" sheet. Column A will list the machine name (will be the same
name and order on each sheet) and column B will list the hours for
that month. I need the final sheet to show the machine name from
Column A and the sum of all the COlumn B info (total hours) for each
month. I'm struggling with the 3D options and other table functions.
I really appreciate any direction someone may be able to give me
Thank god for Deja!!

Steve

 

[Frank Kabel]

Hi
could you provide some more info:
- how are the sheets named
- there's the machine name located
- there#s the value to sum located on each sheet

 

[Steve]

Hi Frank,

Thank you for your reply. Currently, the sheets are just named
"sheet1" through "sheet13". Sheets One through Twelve represent the
months and 13 is the "totals" sheet. The machine name is located in
Column A. The time Value for the machine is located in Column B.
Sheets One through Twelve would basically read:

Column A ColumnB

Row1 Machine 1 35
Row2 Machine 2 17
etc..

Sheet 13 Would show the name in Column A and the total number of hours
in Column B.

Thanks!!

Steve Kaufer

 

[anilsolipuram]

In you 13 th sheet at c2 cell paste the below formulae

I considered the that there are 400 rows in each sheet.



=VLOOKUP(A2,Sheet1!$A$2:$B$400,2,FALSE)+VLOOKUP(A2,Sheet2!$A$2:$B$400,2,FALSE)+VLOOKUP(A2,Sheet3!$A$2:$B$400,2,FALSE)+VLOOKUP(A2,Sheet4!$A$2:$B$400,2,FALSE)+VLOOKUP(A2,Sheet5!$A$2:$B$400,2,FALSE)+VLOOKUP(A2,Sheet6!$A$2:$B$400,2,FALSE)+VLOOKUP(A2,Sheet7!$A$2:$B$400,2,FALSE)+VLOOKUP(A2,Sheet8!$A$2:$B$400,2,FALSE)+VLOOKUP(A2,Sheet9!$A$2:$B$400,2,FALSE)+VLOOKUP(A2,Sheet10!$A$2:$B$400,2,FALSE)+VLOOKUP(A2,Sheet11!$A$2:$B$400,2,FALSE)+VLOOKUP(A2,Sheet12!$A$2:$B$400,2,FALSE

 

[Domenic]

On Sheet13, enter the following formula in B2 and copy down:

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:12"))&"'!A2:A100"),A2,INDIRECT("'Sheet"&ROW(INDIRECT("1:12"))&"'!B2:B100")))

If you decide to change the sheet names, you could use the following
formula instead...

=SUMPRODUCT(SUMIF(INDIRECT("'"&$D$2:$D$13&"'!A2:A100"),A2,INDIRECT("'"&$D$2:$D$13&"'!B2:B100")))

...where $D$2:$D$13 contains the sheet names. Adjust the range
(B2:B100) accordingly.

Hope this helps!

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(SUMIF(INDIRECT("'Sheet" &ROW(INDIRECT("1:12")) &
"'!A1:A100"),A1,INDIRECT("'Sheet" &ROW(INDIRECT("1:12")) &
"'!B1:B100")))

 

[Steve]

Thanks a million for everyone's help. I really appreciate it!!!!!!

Steve