SUM Formula for first 4 values in column that includes blank cells

[chrisb182]

Hi,

This is my 1st post to the site, so apologies if this question has already been asked.

I have a column in a spreadsheet (AA) where values will appear depending on results in other cells.

My question is, is there a formula to SUM the first 4 numbers in the column, bearing in mind that the cells above it contain a formula? (in this instance the SUM of AA70-73).

For reference, next week cell AA69 will contain a value (depending on results elsewhere in the workbook), so in that instance I would need the formula to SUM the values in AA69-72.

I hope this all makes sense!?

Cheers,

Chris

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUM(AA2:INDEX(AA2:AA100,AGGREGATE(15,6,(ROW(AA2:AA100)-ROW(AA2)+1)/(ISNUMBER(AA2:AA100)),4)))

 

[chrisb182]

Hi. That seems to work fine.

Thank you very much for your reply.

Chris

 

[Peter_SSs]

If there are no gaps in the numbers once they start, as it appears from your image and description, this may also be a possibility for you.

With normal entry:

=SUM(AA2:INDEX(AA2:AA100,MATCH(TRUE,INDEX(AA2:AA100<>"",0),0)+3))

Or with Ctrl+Shift+Enter, not just Enter formula confirmation

=SUM(AA2:INDEX(AA2:AA100,MATCH(TRUE,AA2:AA100<>"",0)+3))