SUM of a ROW from last BLANK CELL

[foe001]

I am trying to get a formula that gives me the SUM of an entire row, from last BLANK CELL.

SUM from last BLANK CELL.xlsx
	A	B	C	D	E	F	G	H	I	J	K
5	10.5	10.0	8.2	6.9	9.3	7.1	7.3		10.6	10.2	20.8
6	9.5	9.0			10.5	10.0	9.4	8.2		12.1	12.1
7	10.1	10.3	8.3			9.7	9.6	8.8	10.0	8.3	46.4
8	10.7	11.3	8.7		12.1	10.2	9.0	10.5	9.4	8.4	59.6
9	10.8	9.2	11.1	7.2	5.8	7.0		8.0	9.2	9.4	26.6
10	9.5	10.7	8.6	7.2	6.7	7.5			10.0	8.9	18.9
11	8.2		12.0	8.0	10.4	10.0	8.1	8.0	7.6		0.0
12	11.3	9.8	8.7	5.8	6.6		8.9	9.0	10.0	8.2	36.1
13	11.0	11.3	8.5	7.8	7.7	8.3	8.3		10.2	7.6	17.8
14	8.5	9.4			7.3	8.6	9.4	9.7	8.5	8.3	51.8
Sample

Cell Formulas
Range	Formula
K13,K10,K5:K6	K5	=SUM(I5:J5)
K7	K7	=SUM(F7:J7)
K8,K14	K8	=SUM(E8:J8)
K9	K9	=SUM(H9:J9)
K11	K11	=SUM(J11)
K12	K12	=SUM(G12:J12)

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=SUM(INDEX(B5:J5,AGGREGATE(14,6,(COLUMN(B5:J5)-COLUMN(B5)+1)/(B5:J5=""),1)):J5)

 

[foe001]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=SUM(INDEX(B5:J5,AGGREGATE(14,6,(COLUMN(B5:J5)-COLUMN(B5)+1)/(B5:J5=""),1)):J5)

What does the 14,6 in the formula represent?

 

[Fluff]

It makes the Aggregate function operate like the Large function, but ignores errors.

 

[foe001]

It makes the Aggregate function operate like the Large function, but ignores errors.

It worked on my sample sheet that I provided, but it doesn't work on my actual sheet. It gives me 0.0. All I did was change the ranges in the formula. Do I need to change that number?

 

[Fluff]

Do you have any non blank cells in that range?

 

[foe001]

Nothing wrong with the formula. I found out what is wrong. My fault. I have blank cells after the last cells that have values in them. Can I add something to the formula to only add up to todays date or yesterdays date? Or reference a date in another cell?

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4	12/08/2021	13/08/2021	14/08/2021	15/08/2021	16/08/2021	17/08/2021	18/08/2021	19/08/2021	20/08/2021	21/08/2021	22/08/2021
5	10.5	10	8.2	6.9	9.3	7.1	7.3					0
6	9.5	9			10.5	10	9.4	8.2				38.1
7	10.1	10.3	8.3			9.7	9.6	8.8				28.1
8	10.7	11.3	8.7		12.1	10.2	9	10.5				41.8
9	10.8	9.2	11.1	7.2	5.8	7		8				8
10	9.5	10.7	8.6	7.2	6.7	7.5						0
11	8.2		12	8	10.4	10	8.1	8				56.5
12	11.3	9.8	8.7	5.8	6.6		8.9	9				17.9
13	11	11.3	8.5	7.8	7.7	8.3	8.3					0
14	8.5	9.4			7.3	8.6	9.4	9.7				35
15
Master

Cell Formulas
Range	Formula
L5:L14	L5	=SUM(INDEX(A5:J5,AGGREGATE(14,6,(COLUMN(A5:J5)-COLUMN(A5)+1)/(A5:J5="")/($A$4:$J$4<=TODAY()),1)):J5)

 

[foe001]

If I wanted up to yesterday or a certain date, would I have to use a reference cell?

 

[Fluff]

You could, or for yesterday just subtract 1 from today.