Sum a trailing range that's dynamic to ignore blank cells

[PGuru]

A1:A50 are populated with the number 1.

Delete the entries in rows 17, 26 and 27.

I need a formula that sums the twelve populated rows above it starting at row 13 and continuing to row 50.

So far the best I can get is a SUM(OFFSET that adds on the number of blanks in the default 12 rows via COUNTIF, but that fails every time the 13th row back is also blank. I could of course repeat that trick, but potentially there could be gaps even wider, so that wouldn't prevent inaccuracy.

Can anybody suggest (or point me to a previous thread, I have looked) to sum the most recent 12 populated cells, irrespective of the size that range is required to be?

Thanks in advance!

 

[Gary's Student]

Use a helper column.

In B13 enter:

=IF(A13="",0,1)

in B14 enter:

=IF(A14="",0,IF(COUNT($A$13:A13)=12,0,1))

and copy down. Finally:

=SUMPRODUCT(--(A13:A50)*(B13:B50))

 

[Aladin Akyurek]

@PGuru

Are you saying that you have 1's in column A interspersed with blanks and the last 12 must be summed?

 

[PGuru]

Thanks, I appreciate the suggestion.

A helper column is a bit contentious because there are already 9000 columns where this calculation needs to take place, so a helper column for each would be....unpleasant!

I accept that I may have no choice though.

Your solution returns the answers 0,1, 2 though (depending on the row in question), not 12 (the required result in all cases).

I'll approach it again with an acceptance of a helper column

 

[PGuru]

@PGuru

Are you saying that you have 1's in column A interspersed with blanks and the last 12 must be summed?

The preceding 12. So, row 13 returns 12, row 19 returns 12 (even though row 17 is blank), row 30 returns 12 (even though rows 26 and 27 are blank)

 

[PGuru]

Another way of looking at it would be "how many rows back do I need to look at for COUNTA() over that number of rows to =12?"

 

[Aladin Akyurek]

Another way of looking at it would be "how many rows back do I need to look at for COUNTA() over that number of rows to =12?"

Given this:

Row\Col	A​
1​
2​
3​
4​
5​
6​
7​	1​
8​	1​
9​	1​
10​	1​
11​	1​
12​	1​
13​	1​
14​	1​
15​	x
16​	1​
17​	1​
18​	1​
19​
20​	1​

what are expecting as output?

 

[Aladin Akyurek]

Edit: The question is "what are you expecting as output?"

 

[PGuru]

I'd expect an error on all rows, due to the presence of a character in row 15. If that was a 1 then I'd expect errors in rows 1-17 (as it's not possible to sum twelve numbers at that point), followed by 12 in rows 18-20.

I have sufficient control of the inputs that I can be certain there will be no characters.