Sum in Across Sheet but Different Cell/Row, but the same column (K)

[muhammad susanto]

hi all...

i have question , how to sum/summary in across sheet in different cell
here my explained :

Excel 2007
	D	E
3	sheet name	cell/last row that contains value
4	sheet1	K10
5	sheet2	K12
6	sheet3	K5
7	sheet4	K20
8	summary/total	?
Sheet5

i hope somebody would give a solution/tricks...

m.susanto

 

[snjpverma]

hi all...

i have question , how to sum/summary in across sheet in different cell
here my explained :

Excel 2007

	D	E
3	sheet name	cell/last row that contains value
4	sheet1	K10
5	sheet2	K12
6	sheet3	K5
7	sheet4	K20
8	summary/total	?

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

i hope somebody would give a solution/tricks...

m.susanto

are the above highlighted cells in red volatile or fixed ?

 

[snjpverma]

Try Below formula
=LOOKUP(9.99E+307,Sheet1!K:K)+LOOKUP(9.99E+307,Sheet2!K:K)+LOOKUP(9.99E+307,Sheet3!K:K)+LOOKUP(9.99E+307,Sheet4!K:K)

 

[muhammad susanto]

Try Below formula
=LOOKUP(9.99E+307,Sheet1!K:K)+LOOKUP(9.99E+307,Sheet2!K:K)+LOOKUP(9.99E+307,Sheet3!K:K)+LOOKUP(9.99E+307,Sheet4!K:K)

hi...wow....beautiful answer, but i have over 50 sheets, can to make more shorter formula?
thanks

 

[muhammad susanto]

i have try :

=LOOKUP(9.99E+307,Sheet1:Sheet4!K:K)

but not working...#value!..

any idea?

 

[snjpverma]

i have try :

=LOOKUP(9.99E+307,Sheet1:Sheet4!K:K)

but not working...#value!..

any idea?

Right click the Sheet1 tab and click on Select all sheets and go to cell in the last column i.e. column that will never be used by you. For e.g. in cell XFA1 =LOOKUP(9.99E+307,K:K)
Then ungroup the sheets by clicking on the Ungroup Sheets in the same way. (Very important to ungroup because while in group mode whatever you do happens in all the sheets simultaneously)
Then, wherever you want your final output i.e. the total sum put this formula in that cell. You can change the highlighted part as per your requirement though.
=SUM(Sheet1:Sheet3!XFD1)

 

[muhammad susanto]

hi...

sorry , it's work perfect...i have change

=SUM(Sheet1:Sheet4!XFA1)

thank you very much..

 

[Marcelo Branco]

Maybe...

Formula in E8 of Summary
=SUMPRODUCT(N(INDIRECT("'"&D4:D7&"'!"&E4:E7)))

Hope this helps

M.

 

[muhammad susanto]

Maybe...

Formula in E8 of Summary
=SUMPRODUCT(N(INDIRECT("'"&D4:D7&"'!"&E4:E7)))

Hope this helps

M.

hi Marcelo, Fantastic!!!!
i have a question, how to easy pull/extract name of sheet in a list included the cell that contains value...
e.g..

Sheet1---K10
Sheet2---K12
etc.....

 

[Marcelo Branco]

See if this helps
https://www.extendoffice.com/documents/excel/2653-excel-dynamic-list-of-worksheet-names.html#a1

M.