Excel Formula to sum 12 most recent cells

0
votes

I have an Excel formula question that I was hoping you could help me with.

Basically I have a set of numbers in the second row of my spreadsheet from A2 to CC2. However, as of now only A2 to BA2 are populated with numbers. BB2 onwards will be populated in the coming weeks. I need to have a formula in cell CD2 which only sums up the last 12 populated cells in row 2. In this case it would the sum of AP2 to BA2, but next week it would be AQ2 to BB2 & so on.

I would really appreciate it if you could please help me out.

Thanks in advance, Ducky

3
excelexcel-formulasum
Search on here, similar questions have been asked. – Solar Mike
But I would find the last populated cell and use offset()... – Solar Mike
@SolarMike Offset is volatile. There are better formulas. – teylyn
@teylyn but it would work and something for the op to try. – Solar Mike

3 Answers

4
votes

use:

=SUM(INDEX(A2:CC2,MATCH(1E+99,A2:CC2)-11):INDEX(A2:CC2,MATCH(1E+99,A2:CC2)))

It will find the last cell in the range that has a number and sum that cell and the 11 cells to the left.

0
votes

Or, this shorter one,

in CD2 :

=SUM(OFFSET(A2,,MATCH(9.9E+307,A2:CC2)-1,,-12))

p.s. Although Offset is volatile, it still can consider in case of formulas used not in a huge quantity.

0
votes

A little bit less efficient solution (Array formula - Ctrl+Shift+Enter), but might also prove useful:

=SUMPRODUCT(IF(COLUMN($A$2:$CC$2)>(COUNTA($A$2:$CC$2)-12),$A$2:$CC$2,0))

It only takes into account last 12 columns - IF(COLUMN($A$2:$AA$2)>(COUNTA($A$2:$AA$2)-12),... and then it sums their respective values.

enter image description here