CODEWITHSUNDEEP
excelformula
Sultanen
Sultanen

Reputation: 3412

Excel - Sum column until it reaches a cell that is not a number

I want to be able to use "sub sums" that sums up every number until it reaches a formula or "end of page". The range has to be dynamic, if a new line is inserted it has to be included in the range even if its inserted at the end of the range.

=SUM(XXX) = 20
5

5
5
5
=SUM(XXX) = 15
5
5
5

How can i achive this without using VBA?

Upvotes: 0

Views: 197

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Put the following formula in each place you want the formula:

=SUM(INDEX(A:A,ROW()+1):INDEX(INDEX(A:A,ROW()+1):INDEX(A:A,MATCH(1E+99,A:A)),IFERROR(MATCH(TRUE,INDEX((ISFORMULA(INDEX(A:A,ROW()+1):INDEX(A:A,MATCH(1E+99,A:A)))),),0)-1,MATCH(1E+99,A:A)-ROW())))

enter image description here

Upvotes: 2

Related Questions