Sum (different sheets)

  • Thread starter Thread starter an
  • Start date Start date
A

an

Hello!

I have, 3 Sheet3 with next data blocks:

A B
1 N 1 Val1
2 N 2 Val2
3 ... ...

I would like a formulae to SUM numeric values in other
sheet such as:

N 1 = Val1(Sheet1) + Val1(Sheet2) + Val1(Sheet3)
N 2 = Val2(Sheet1) + Val2(Sheet2) + Val2(Sheet3)
....

Somes N without numeric Values.

In sheet to SUM I have all N's although without values in
origin (Result=0).

Thanks in advance
an
 
Thanks for your replay.

Sorry for my deficient explanation.

Your solution, return Total of block A1:A20 of the all
sheets, but I need to obtain the SUM for rows.
In other words: Column A (with or without Names) and
Column B (with or without Values) and to sum Values of all
shetts but regarding each Name.
SUMName1= Value of Name1(Sheet1)+ Value of Name1(Sheet2)+
Value of Name1 (Sheet3)...

Thanks.
an
 
Hi
now you have lost me. could you please post some example data (plain
text - no attachment please)
 
Sorry for my delay.

Sheet1: data block A3:B5
Names Values
Na 10
Nb 15
Nc 20

Sheet2: data block A3:B5
Names Values
.... .... (without Names and values)
Nb 25
Nc 30

Sheet3: data block A3:B5
Names Values
Na 10
.... .... (without Names and values)
Nc 0

To obtain in

SheetX: SumResults
AllNames Values
Na 20 (10+0+10)
Nb 40 (15+25+0)
Nc 50 (20+30+0)
.... .... (...)
.Nx. .Vn. (...+...+...+...)

However I tied array formulaes but don´t work(...)

Thanks.
an
 
Hi
some solutions:
1. Harlan Grove showed a formula approach for a conditional sum accross
multiple worksheets. Have a look at this thread
http://tinyurl.com/2manj

2. You may also try to download the free add-in Morefunc.xll
(http://longre.free.fr/english). The function THREED converts a 3D
array to a 2D array. e.g. you may use the following formula on sheet X
=SUM((THREED('sheet1:sheet3'!A1:A1000)="Na")*(THREED('sheet1:sheet3'!B1
:B1000)))

enter this as array formula (CTRL+SHIFT+ENTER). This will sum all
values from column B in which column A contains our criteria. You can
replace the hardcode "Na" with a cell reference on sheet x. Something
like the following formula in B1 on sheetx:
=SUM((THREED('sheet1:sheet3'!$A$1:$A$1000)=A1)*(THREED('sheet1:sheet3'!
$B$1:$B$1000)))
and copy down

--
Regards
Frank Kabel
Frankfurt, Germany

Sorry for my delay.

Sheet1: data block A3:B5
Names Values
Na 10
Nb 15
Nc 20

Sheet2: data block A3:B5
Names Values
.... .... (without Names and values)
Nb 25
Nc 30

Sheet3: data block A3:B5
Names Values
Na 10
.... .... (without Names and values)
Nc 0

To obtain in

SheetX: SumResults
AllNames Values
Na 20 (10+0+10)
Nb 40 (15+25+0)
Nc 50 (20+30+0)
.... .... (...)
.Nx. .Vn. (...+...+...+...)

However I tied array formulaes but don´t work(...)

Thanks.
an
 
Ok, Frank.

Many thanks.
an
-----Original Message-----
Hi
some solutions:
1. Harlan Grove showed a formula approach for a conditional sum accross
multiple worksheets. Have a look at this thread
http://tinyurl.com/2manj

2. You may also try to download the free add-in Morefunc.xll
(http://longre.free.fr/english). The function THREED converts a 3D
array to a 2D array. e.g. you may use the following formula on sheet X
=SUM((THREED('sheet1:sheet3'!A1:A1000)="Na")*(THREED ('sheet1:sheet3'!B1
:B1000)))

enter this as array formula (CTRL+SHIFT+ENTER). This will sum all
values from column B in which column A contains our criteria. You can
replace the hardcode "Na" with a cell reference on sheet x. Something
like the following formula in B1 on sheetx:
=SUM((THREED('sheet1:sheet3'!$A$1:$A$1000)=A1)*(THREED ('sheet1:sheet3'!
$B$1:$B$1000)))
and copy down

--
Regards
Frank Kabel
Frankfurt, Germany

Sorry for my delay.

Sheet1: data block A3:B5
Names Values
Na 10
Nb 15
Nc 20

Sheet2: data block A3:B5
Names Values
.... .... (without Names and values)
Nb 25
Nc 30

Sheet3: data block A3:B5
Names Values
Na 10
.... .... (without Names and values)
Nc 0

To obtain in

SheetX: SumResults
AllNames Values
Na 20 (10+0+10)
Nb 40 (15+25+0)
Nc 50 (20+30+0)
.... .... (...)
.Nx. .Vn. (...+...+...+...)

However I tied array formulaes but don´t work(...)

Thanks.
an
(Sheet2)

.
Click to expand...
 
Back
Top