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,

=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)



"Adrian" wrote in message
...
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,

Try...

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

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

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

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

Hope this helps!

In article ,
Adrian wrote:

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,

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