Help to sum all cells above until blank cell

[RemyMoo]

I'm working on a pricing spreadsheet, and I'm likely stressing on this too much, but after spending this much time on it, would love for it to be perfect.
On column L, I currently have this formula:
L: =IFERROR(IFS($A5="TOTAL",SUM(L2:L4),($H5+$I5+$J5+$K5)=0,"",($H5+$I5+$J5+$K5)>0,($H5+$I5+$J5+$K5),ISBLANK($A5:$K5),""),"")

Instead of the SUM(L2:L4) argument, I'd like it to sum all cells above, but stop once a blank cell is hit. Is this possible? There could be one line to sum, or 10+. I'm not familiar with arrays or VBA, but willing to try!

I feel like my function is probably clunky, but this is the only way I could get it to work so far.

Likely this explaination is not needed, but I'd like the cell to be blank if an error or 0 result, (nothing entered in A5:K5). If A5 has "TOTAL" in it, to sum everything above until the blank cell. If anything other than TOTAL in A, then sum H5:K5.

First time posting here, so I'm hoping the mini sheet works! Please let me know if any other info is needed. Thanks so much!

EXCEL HELP SAMPLE.xlsx
	G
11
NEW WORKSHEET

Cell Formulas
Range	Formula
G11	G11	=IFERROR(VLOOKUP(F11,'https://d.docs.live.net/2af8e22389817ea9/Projects/DOCUMENTS^J QUOTES^J ETC/PRICING WORKBOOK - FEB 2023.xlsx'!Table1[[#All],[ITEM NAME]:[DATE PRICED]],12,FALSE),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:S	Expression	=$A1="TOTAL"	text	YES

 

[RemyMoo]

I feel like I maybe did the mini sheet incorrectly, and can't see how to edit my post, so trying again!

EXCEL HELP SAMPLE.xlsx
	L
5	$ 41.31
NEW WORKSHEET

Cell Formulas
Range	Formula
L5	L5	=IFERROR(IFS($A5="TOTAL",SUM(L2:L4),($H5+$I5+$J5+$K5)=0,"",($H5+$I5+$J5+$K5)>0,($H5+$I5+$J5+$K5),ISBLANK($A5:$K5),""),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:S	Expression	=$A1="TOTAL"	text	YES

 

[Joe4]

I believe that you need to select the entire range you want to show first, otherwise it just shows the active cell (like you posted both times).

 

[RemyMoo]

I believe that you need to select the entire range you want to show first, otherwise it just shows the active cell (like you posted both times).

Shoot, thank you!!

EXCEL HELP SAMPLE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	PROJECT	TYPE	DIM1	DIM2	SQ INCH	MATERIAL	$ PER SQ INCH	MATERIAL	LASER TIME	OTHER	TIME	COST	COST X2	COST X2.5	COST X3	SELL	PROFIT $	PROFIT %	NOTES
2	SIGN - BACKER	SIGN	24	24	576	ACRYLIC - BLK WHT CLR	$ 0.02962	$ 17.06	1.75		$ 10.00	$ 28.81	$ 57.63	$ 72.03	$ 86.44
3	SIGN - LOGO	SIGN	24	5.75	138	ACRYLIC - BLK WHT CLR	$ 0.02962	$ 4.09	5.50			$ 9.59	$ 19.18	$ 23.97	$ 28.76
4	SIGN - ADH	SIGN	24	5.75	138	ADHESIVE	$ 0.02108	$ 2.91				$ 2.91	$ 5.82	$ 7.27	$ 8.73
5	TOTAL											$ 41.31	$ 82.62	$ 103.27	$ 123.93	$ 95.00	$ 53.69	56.52%
6
7	KEYCHAIN	KEYCHAIN	3	1.5	4.5	ACRYLIC - BLK WHT CLR	$ 0.02962	$ 0.13	2.00			$ 2.13	$ 4.27	$ 5.33	$ 6.40
8	KEYCHAIN	KEYCHAIN	1	1	1	KEYCHAIN RING	$ 0.09444	$ 0.09				$ 0.09	$ 0.19	$ 0.24	$ 0.28
9	KEYCHAIN	KEYCHAIN	1	1	1	KEYCHAIN SNAPS	$ 0.26250	$ 0.26			$ 1.00	$ 1.26	$ 2.53	$ 3.16	$ 3.79
10	KEYCHAIN	KEYCHAIN	1	1	1	PACKAGING	$ 0.17272	$ 0.17				$ 0.17	$ 0.35	$ 0.43	$ 0.52
11	TOTAL											$ 3.66	$ 7.33	$ 9.16	$ 10.99	$ 9.00	$ 5.34	59.30%
NEW WORKSHEET

Cell Formulas
Range	Formula
L2:L3	L2	=IFERROR(IFS($A2="TOTAL",SUM(#REF!),($H2+$I2+$J2+$K2)=0,"",($H2+$I2+$J2+$K2)>0,($H2+$I2+$J2+$K2),ISBLANK($A2:$K2),""),"")
M2:M4	M2	=IFERROR(IFS($A2="TOTAL",SUM(#REF!),($H2+$I2+$J2+$K2)=0,"",($H2+$I2+$J2+$K2)>0,($H2+$I2+$J2+$K2)*2,ISBLANK($A2:$K2),""),"")
N2:N4	N2	=IFERROR(IFS($A2="TOTAL",SUM(#REF!),($H2+$I2+$J2+$K2)=0,"",($H2+$I2+$J2+$K2)>0,($H2+$I2+$J2+$K2)*2.5,ISBLANK($A2:$K2),""),"")
O2:O4	O2	=IFERROR(IFS($A2="TOTAL",SUM(#REF!),($H2+$I2+$J2+$K2)=0,"",($H2+$I2+$J2+$K2)>0,($H2+$I2+$J2+$K2)*3,ISBLANK($A2:$K2),""),"")
M6:O6,L4:L10	L4	=IFERROR(IFS($A4="TOTAL",SUM(L1:L3),($H4+$I4+$J4+$K4)=0,"",($H4+$I4+$J4+$K4)>0,($H4+$I4+$J4+$K4),ISBLANK($A4:$K4),""),"")
M5,M11	M5	=IFERROR(IFS($A5="TOTAL",SUM(M1:M4),($H5+$I5+$J5+$K5)=0,"",($H5+$I5+$J5+$K5)>0,($H5+$I5+$J5+$K5)*2,ISBLANK($A5:$K5),""),"")
N5,N11	N5	=IFERROR(IFS($A5="TOTAL",SUM(N1:N4),($H5+$I5+$J5+$K5)=0,"",($H5+$I5+$J5+$K5)>0,($H5+$I5+$J5+$K5)*2.5,ISBLANK($A5:$K5),""),"")
O5,O11	O5	=IFERROR(IFS($A5="TOTAL",SUM(O1:O4),($H5+$I5+$J5+$K5)=0,"",($H5+$I5+$J5+$K5)>0,($H5+$I5+$J5+$K5)*3,ISBLANK($A5:$K5),""),"")
M7:M10	M7	=IFERROR(IFS($A7="TOTAL",SUM(M4:M6),($H7+$I7+$J7+$K7)=0,"",($H7+$I7+$J7+$K7)>0,($H7+$I7+$J7+$K7)*2,ISBLANK($A7:$K7),""),"")
N7:N10	N7	=IFERROR(IFS($A7="TOTAL",SUM(N4:N6),($H7+$I7+$J7+$K7)=0,"",($H7+$I7+$J7+$K7)>0,($H7+$I7+$J7+$K7)*2.5,ISBLANK($A7:$K7),""),"")
O7:O10	O7	=IFERROR(IFS($A7="TOTAL",SUM(O4:O6),($H7+$I7+$J7+$K7)=0,"",($H7+$I7+$J7+$K7)>0,($H7+$I7+$J7+$K7)*3,ISBLANK($A7:$K7),""),"")
L11	L11	=IFERROR(IFS($A11="TOTAL",SUM(L7:L10),($H11+$I11+$J11+$K11)=0,"",($H11+$I11+$J11+$K11)>0,($H11+$I11+$J11+$K11),ISBLANK($A11:$K11),""),"")
Q2:Q11	Q2	=IFERROR(IF(P2>0,P2-L2,""),"")
R2:R11	R2	=IFERROR(Q2/P2,"")
H2:H5,H7:H11	H2	=IFERROR((G2*E2),"")
G2:G11	G2	=IFERROR(VLOOKUP(F2,'https://d.docs.live.net/2af8e22389817ea9/Projects/DOCUMENTS^J QUOTES^J ETC/PRICING WORKBOOK - FEB 2023.xlsx'!Table1[[#All],[ITEM NAME]:[DATE PRICED]],12,FALSE),"")
E2:E11	E2	=IF(C2*D2=0,"",C2*D2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:S	Expression	=$A1="TOTAL"	text	YES