How to sum till blank cell in dynamic way?

[MandeepBajimaya]

I have come across a situation where I want to add the column till there is space. Eg: Sum from B4 till there is a blank cell. Till now the value is till B6, but later it can be added to B7, B8. I cannot use an excel table in my workbook. Please refer to the attached screenshot. I need a total amount of Plant & Machinery, Furniture & Fixtures from the right sheet in my left sheet. This has to be dynamic as we keep adding machines or furniture in the coming days. What can be the best excel solution?

 

[Peter_SSs]

Welcome to the MrExcel board!

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are (without having to manually type it out). Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

We can't see very much in that image but it looks like there might be a total value in the right sheet in row 32. If so, how would the logic work to get a total for "Computer & Office Equipment" if there is not a blank immediately below those values?

Your profile shows both Excel 2019 & 365. Does any suggestion made need to work in both versions or would a solution in 365 only be sufficient?

 

[MandeepBajimaya]

Welcome to the MrExcel board!

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are (without having to manually type it out). Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

We can't see very much in that image but it looks like there might be a total value in the right sheet in row 32. If so, how would the logic work to get a total for "Computer & Office Equipment" if there is not a blank immediately below those values?

Your profile shows both Excel 2019 & 365. Does any suggestion made need to work in both versions or would a solution in 365 only be sufficient?

Hi Sir,

This was overlooked because i was searching for answer in above rows. Assuming if row 32 was blank, what logic could be used to solve the issue? I would prefer the solution for both Excel 2019 & 365 because not all my collegues use 365.

Financials Format NAS for ME - V2.xlsx
	A	B	C														Q	R	S	T	U	V	W	X	Y
16	Total (A)		-															Cost	Land and Building	Plant & Machinery	Furniture & Fixture	Computer & Office Equipment	Under Construction	Intangibles	Total
17	Pool B																	Balance at Shrawan 2077	-	-	-	-	-	-	-
18	Plant & Machinery																	Additions	-	-	-	-	-	-	-
19	Inverter battery	25%	1,200															Disposals	-	-	-	-	-	-	-
20	Key box	25%	1,200															Balance at Ashad 2078	-	-	-	-	-	-	-
21
22	Furniture & Fixture																	Depreciation and Impariment losses
23	Office furniture	25%	25,000															Balance at 01 Shrawan 2077	-	-	-	-	-	-	-
24																		Depreciation charged for the year	-	-	-	-	-	-	-
25	Computer & Office Equipment																	Adjustment due to Impairment Losses	-	-	-	-	-	-	-
26	Kitchen Equipment	25%	5,000															Disposals	-	-	-	-	-		-
27	Speaker and microphone	25%	200															Balance at Ashadh 2078	-	-	-	-	-	-	-
28	Thermal printer	25%	700
29	Musical Instrument	25%	250
30	Carpet & Curtain	25%	150
31	Electric Equipments	25%	-
32
33	Total (B)		33,700
Schedule FA

Cell Formulas
Range	Formula
C16	C16	=C15+C10+C11
R17	R17	="Balance at Shrawan "&LEFT(PY_BS,4)
X17	X17	='Schedule FA'!T68
Y23:Y27,Y17:Y20	Y17	=SUM(S17:Cell_Left)
X18	X18	='Schedule FA'!U68
X19	X19	='Schedule FA'!V68
U18	U18	='Schedule FA'!X43
S17	S17	='Schedule FA'!T26
S18	S18	='Schedule FA'!X26
S19	S19	='Schedule FA'!Y26
R20	R20	="Balance at Ashad "&LEFT(CY_BS,4)
S20:W20	S20	=S17+S18-S19
X20	X20	='Schedule FA'!W68
R23	R23	="Balance at 01 Shrawan "&LEFT(PY_BS,4)
U24	U24	='Schedule FA'!AC43
X23	X23	='Schedule FA'!X68
X24	X24	='Schedule FA'!Y68
R27	R27	="Balance at Ashadh "&LEFT(CY_BS,4)
U27,X27	U27	=U23+U24-U25-U26
C33	C33	=SUM(C17:C31)

Named Ranges
Name	Refers To	Cells
CY_BS	=Index!$I$11	R20, R27
PY_BS	=Index!$I$13	R17, R23

 

[Peter_SSs]

Thanks for the XL2BB data.

I'm not quite sure where you wanted the results or what column you are trying to sum (you said earlier col B but that doesn't seem right to me) but see if you can adapt this.

21 06 02.xlsm
	A	B	C	D	E	F
16	Total (A)		-
17	Pool B
18	Plant & Machinery				Plant & Machinery	2400
19	Inverter battery	25%	1,200		Furniture & Fixture	25000
20	Key box	25%	1,200		Computer & Office Equipment	6300
21
22	Furniture & Fixture
23	Office furniture	25%	25,000
24
25	Computer & Office Equipment
26	Kitchen Equipment	25%	5,000
27	Speaker and microphone	25%	200
28	Thermal printer	25%	700
29	Musical Instrument	25%	250
30	Carpet & Curtain	25%	150
31	Electric Equipments	25%	-
32
33	Total (B)		33,700
Sum to blank

Cell Formulas
Range	Formula
F18:F20	F18	=SUM(INDEX(C:C,MATCH(E18,A:A,0)):INDEX(C:C,AGGREGATE(15,6,ROW(A$16:A$33)/((A$16:A$33="")*(ROW(A$16:A$33)>MATCH(E18,A:A,0))),1)))

 

[MandeepBajimaya]

Wow, This is what I wanted. Thank you so much for your help.

Actually, I am trying to make a format for financial statements where when I update the trial balance financial are prepared automatically as much as possible. Are there any videos for such.

 

[Peter_SSs]

Wow, This is what I wanted. Thank you so much for your help.

You're welcome. Thanks for the follow-up.

Are there any videos for such.

I don't know. You could Google that as easily as I could.