Add values in a column until a certain point in excel and repeat

If you want the sum to be in the first row of each group you can use this formula:

=IF(AND(M2="Flag",M1<>"Flag"),SUM(K2:INDEX(K2:INDEX(K:K,MATCH(1E+99,K:K)+1),MATCH(TRUE,INDEX(M2:INDEX(M:M,MATCH(1E+99,K:K)+1)="",),0)-1)),"")

Breaking it down:

The two INDEX(K:K,MATCH(1E+99,K:K)+1) find the last cell in column K that has a number and sets that as the extent of the referenced range This way the formula is dynamic, as new rows get added the formula will adjust automatically.

Then inside the Sum we start with the cell that is in the same row, the K2 is relative and as the formula moves down so does the reference. The end cell in the range is set with another INDEX/MATCH.

This time we are looking for the first cell from the row the formula is in to be empty in column M. We set that row -1 as the last row for the SUM().

The IF() merely makes any we want to do the sum and the rest to be an empty string.

---

If you want it located at the bottom of the group then use this array formula:

=IF(AND(M2="Flag",M3=""),SUM(INDEX($K$1:K2,IFERROR(MATCH(2,IF($M$1:M2="",1)),1)+1):K2),"")

Being an array formula it must be confirmed with Ctrl-Shift-Enter when exiting edit mode. If done correctly the Excel will put {} around the formula.

This time we are looking for the last empty space in M above the current row to set the first.

The IFERROR is to deal with the title row, if the row just below has "Flag".

Recent Questions...