Blank Rows Causing Errors

[Steve 1962]

Hi

I have a VLOOKUP formula in column B which I want to continue to use, with a running total in column D. Problem is when there is blank rows between transactions, causing the Running Total in column D to error. Is there a way to modify column D formula to not error and accommodate the blank rows, keeping the Running Total correct.

Thanks

Steve

Book1
	A	B	C	D	E
1		Out	In	Balance
2	Income		2000	#VALUE!	Cell D2 to show 2000
3	Income		2000	#VALUE!	Cell D3 to show 4000
4				#VALUE!	Cell D4 to show blank
5	Expense	-400		#VALUE!	Cell D5 to show 3600
6				#VALUE!	Cell D6 to show blank
7	Expense	-50		#VALUE!	Cell D7 to show 3550
8				#VALUE!	Cell D8 to show blank
9				#VALUE!	Cell D9 to show blank
10	Expense	-25		#VALUE!	Cell D10 to show 3525
Sheet1

Cell Formulas
Range	Formula
B2:B4, B8:B9, B6	B2	=IFNA(VLOOKUP(A2,Expense_List,7,FALSE)*-1,"")
D2:D10	D2	=D1+C2+B2

 

[Legacy 143009]

How about to change this formula:

=IFNA(VLOOKUP(A2,Expense_List,7,FALSE)*-1,"")

to this:

=IFNA(VLOOKUP(A2,Expense_List,7,FALSE)*-1,0)

Then you can apply a number formatting like this to hide zeros:
0;0;;@

 

[Peter_SSs]

Try like this

23 11 22.xlsm
	A	B	C	D	E
1		Out	In	Balance
2	Income		2000	2000	Cell D2 to show 2000
3	Income		2000	4000	Cell D3 to show 4000
4					Cell D4 to show blank
5	Expense	-400		3600	Cell D5 to show 3600
6					Cell D6 to show blank
7	Expense	-50		3550	Cell D7 to show 3550
8					Cell D8 to show blank
9					Cell D9 to show blank
10	Expense	-25		3525	Cell D10 to show 3525
Steve 1962

Cell Formulas
Range	Formula
D2:D10	D2	=IF(A2="","",SUM(B$2:C2))

 

[RustamMika]

Formula in D2 cell
=IF(A2<>"",SUM($C$2:C2)+SUM($B$2:B2),"")

 

[Steve 1962]

Thanks very much to Flashbond, RustamMika and Peter. Appreciate the help - all fixed and working fine now.

Extra thanks to Peter who has helped me on many occassions.

Steve

 

[Peter_SSs]

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