How to I sum different data from different sheets

[Adrian]

Hello. I have a workbook with 12 sheets. In the first one I have in column A:
x , y, z, x, a, z ....and in column B: 100 $, 300 $, .... In the second sheet
I have in column A: x, a, b, c, y, x.... and in column B: 200 $, 400 $....Ans
so on to the 12th sheet. I want in the 13rd sheet in the column A to have x,
y, z ...(it's not important the order ) and in column B to have sum from all
the sheets for x, y, z.....I want to put an accent: in the sheet values from
column A can repeat.

ex:
sheet 1
A B
1 x 100 $
2 y 300 $
3 z 100 $
4 x 200 $
4 ....

sheet 2
A B
1 x 100 $
2 a 300 $
3 b 200 $
4 a 100 $
5.....

sheet.......

sheet 13
A B
1 x (sum sheet1 + sheet 2+ ....sheet 12 )
2 y (sum sheet1 + sheet 2+ ....sheet 12 )
3 z (sum sheet1 + sheet 2+ ....sheet 12 )
........

thank u,

 

[Bob Phillips]

=SUMPRODUCT(--(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1:12"))&"!A2:A20"),"x",INDIRECT("Sheet"&ROW(INDIRECT("1:12"))&"!B2:B20"))))

etc.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Domenic]

Try...

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

If the actual sheet names differ from Sheet1, Sheet2, Sheet3, etc., try
the following instead...

=SUMPRODUCT(SUMIF(INDIRECT("'"&$G$1:$G$12&"'!A1:A100"),A1,INDIRECT("'"&$G
$1:$G$12&"'!B1:B100")))

....where G1:G12 contains the sheet names. Adjust the ranges accordingly.

Hope this helps!

 

[Herbert Seidenberg]

Or use Pivot Table.
No formulas needed.
Data can be of different size,
at different locations, and PT will
automatically adjust.
Excel 2007, but will convert to 2003.
http://www.savefile.com/files/1513255