Excel tutorials

How to Sum Up all the values in a sequence, up to the first empty cell in the column

In case you have data organization so that the cell ranges are separated and you need to SUM all the values in an uninterrupted sequence to the first empty cell, then try to use the CSE formulas listed in this tutorial.

In the picture below you can see the organization of the data table. Notice two tables that contain the values in the 'D' column we want to sum. We have two lines that we need to sum up, but we use one formula. In the first table, we have eight values in a row. Thus, the ninth cell (D10) is the first empty cell in the sequence after the values.
When it comes to the second table shown, then we have a sequence of five filled cells. So the sixth cell (D17) is the first empty cell after an unbroken string.

Sum of all values in a sequence to the first empty cell in the column 

The ARRAY formula in 'D1' cell is the following: (Copy this formula to 'D11' cell and so on)

=SUM(D2:INDEX(D2:$D$102;MATCH(TRUE;(D2:$D$102="");0)-1))

To create a dynamic range of cells using the INDEX function in which we are nestled MATCH function. If you do not know how nested functions work, see the tutorial with the nested function example in Excel Formulas.

The matched formula MATCH(TRUE;(D2:$D$102="");0)-1 as a result returns the number 8. This value is the number of rows in which we have the values of the respective cell range. In fact, if a more precise formula returns value 9 because it takes the first empty cell into account, this value is reduced to 1.

=MATCH(TRUE;{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE};0)-1

To help you understand how the formula works step by step, then use the Evaluate Formula and F9 key.

The next nested formula is an INDEX function. This formula determines the last filled row, ie the address where the last value is found, so in combination with 'D2' returns the sum of all the values in a given cell range from one column.

=INDEX({30;30;30;30;30;30;30;30;0;400;80;80;80;80;80;"";0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0};8)

Notice the zero in the formula above. This is the first empty cell after a string of values. Finally, we have a SUM formula result.

=SUM({30;30;30;30;30;30;30;30})

SUM values in sequence up to the first non-filled cell in the column

The second ARRAY formula you can use is the following:

=SUM(OFFSET(D2;0;0;MATCH(TRUE;LEN($D2:$D$102)=0;0)-1))

The nested LEN function (as a CSE formula) within the MATCH function returns the TRUE or FALSE result and asks for the first occurrence of the zero in the array.

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

or

=LEN({30;30;30;30;30;30;30;30;0;400;80;80;80;80;80;"";0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0})=0

Similar to the first formula we have two nested formulas within the SUM function. In this case, the MATCH function searches for the first "TRUE" expression. Notice in the formula below that this expression (true) appears in ninth position. So, the result is 9 and we downplay it for 1 and the end result is 8. (you count how many FALSE terms are bold).

=MATCH(TRUE;{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE};0)-1

The MATCH function shown above, returns the result of an argument [height] OFFSET function (see arguments Offset function).

The result of the OFFSET function is as follows: =SUM({30;30;30;30;30;30;30;30}) (note that we have 8 values that SUM function will Sum Up).

Please note that VOLATILE functions are displayed in this tutorial: INDEX and OFFSET that can slow down Excel if you have a lot of data.

And also in the attachment to this tutorial, you have the option to download the file on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.