Dynamic Formula to Sum Cells in Row

[Gos-C]

Hi all,

I have the following formula:

{="=SUM(C"&ROW(OFFSET(APR!$J$1,MAX(ROW(APR!$J:$J)*(APR!$J:$J<>""))-12,0))&":N"&ROW(OFFSET(APR!$J$1,MAX(ROW(APR!$J:$J)*(APR!$J:$J<>""))-12,0))&")"}

It returns:

=SUM(C408:N408)

But I need it to return the value (i.e, the sum of cells C408 to N408).

Any how?

Thank you,
Gos-C

 

[T. Valko]

Hi Steve,

I have several similarly formatted reports, which varies in length, and I want to sum a range in the 12th from last row of each report.

Gos-C

It returns:
=SUM(C408:N408)

So, the data in column C is numeric only?

Are there any empty cells within the range of column C?

 

[Gos-C]

Hi T. Valko,

No, C4 is text (the column title). And, yes, there are blank cells in column C.

Steve, I had try already tried your suggestions without success - and I did it again with the same result.

Gos-C

 

[Steve=True]

I don't think anything is different.

This time i created my own sheet called APR

i then changed each reference to APR.

I added a row in J on APR and it moved and added C:N on APR

by the way, do NOT hit ctrl+Shift+enter. This is NOT an Array. (just in case)

Here is that formula that i just changed and retested:

=SUM(INDIRECT(("C"&IF(ISERROR(MATCH(9.999999E+306,APR!J:J)),MATCH("*",APR!J:J,-1),
IF(ISERROR(MATCH("*",APR!J:J,-1)),MATCH(9.999999E+306,APR!J:J),
MAX(MATCH(9.999999E+306,APR!J:J),MATCH("*",APR!J:J,-1))))-12)&":n"&IF(ISERROR(MATCH(9.999999E+306,APR!J:J)),MATCH("*",APR!J:J,-1),
IF(ISERROR(MATCH("*",APR!J:J,-1)),MATCH(9.999999E+306,APR!J:J),
MAX(MATCH(9.999999E+306,APR!J:J),MATCH("*",APR!J:J,-1))))-12))

 

[T. Valko]

Hi T. Valko,

No, C4 is text (the column title). And, yes, there are blank cells in column C.

Steve, I had try already tried your suggestions without success - and I did it again with the same result.

Gos-C

Ok, is this what you're trying to do?

Find the last (bottom most) numeric value in column C. From there, going back up the column a total of 12 rows and then sum from that row in column C to that row in column N.

 

[Gos-C]

Yes

 

[T. Valko]

Yes

Ok, let's assume this is your data:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N
5	_	19	84	75	21	22	70	94	19	73	76	73	7
6	_	7	84	61	12	97	21	33	73	4	68	31	76
7	_	75	97	90	10	64	2	16	83	98	48	14	36
8	_	40	94	18	59	54	25	83	81	31	92	82	48
9	_	67	70	30	56	38	74	89	85	50	23	6	20
10	12	59	66	20	2	82	19	54	0	84	52	33	47
11	11	54	20	51	16	14	29	44	76	45	34	75	91
12	10	27	32	35	39	91	70	94	3	11	23	42	21
13	9	37	77	78	73	66	52	32	43	2	93	94	69
14	8	98	37	3	54	21	10	85	62	53	36	32	84
15	7	51	11	98	60	68	67	75	55	91	27	70	80
16	6	46	78	12	79	97	5	9	37	61	37	74	76
17	5	85	6	38	90	48	63	8	89	96	42	47	0
18	4	74	98	69	81	33	41	25	21	8	26	90	56
19	3	87	80	1	5	65	65	64	74	37	38	64	41
20	2	11	93	84	32	43	54	3	95	67	61	54	76
21	1	75	93	9	26	92	1	42	42	24	99	18	78
Sheet1

To get the sum of the row that's highlighted, the 12th from the last row...

=SUM(INDEX(C:N,MATCH(1E100,C:C)-11,0))

 

[Steve=True]

T. Valko, that was Elegant!

 

[Gos-C]

Great! Works perfectly!

Thank you very much, T. Valko.

Sincerely,
Gos-C

 

[T. Valko]

Great! Works perfectly!

Thank you very much, T. Valko.

Sincerely,
Gos-C

You're welcome. Thanks for the feedback!

 

[T. Valko]

T. Valko, that was Elegant!

Thanks!