How to sum cell values in a column until blank cell reached?
If you have a list of numbers which populated with some blank cells, and now, you need to sum the numbers above or below until blank cells in this list to get the following results. Do you have any quick or easy ways to solve this job in Excel?
Sum cell values above until blank cell
Sum cell values below until blank cell
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
To sum cell values below until blanks, here is an array formula, please do as this:
1. Enter this formula into a blank cell beside your numbers column, E1, for example.
=IF(D1="",SUM(D1:INDEX(D1:$D$17,MATCH(TRUE,(D2:$D$17=""),0))),"") (D1, D2 are the first two cells in your column, D17 is the last blank cell in the number column).
2. Then press Ctrl + Shift + Enter keys together, and then drag the fill handle down to the cells that you want to sum numbers below until blanks, see screenshot:
If you need to sum cell values above until blank cells, the following VBA code may do you a favor.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Sum cell values above until blanks:
Sub InsertTotals() 'Updateby Extendoffice 20160829 Dim xRg As Range Dim i, j, StartRow, StartCol As Integer Dim xTxt As String On Error Resume Next xTxt = ActiveWindow.RangeSelection.AddressLocal Set xRg = Application.InputBox("please select the cells:", "Kutools for Excel", xTxt, , , , , 8) If xRg Is Nothing Then Exit Sub StartRow = xRg.Row StartCol = xRg.Column For i = StartCol To xRg.Columns.Count + StartCol - 1 For j = xRg.Row To xRg.Rows.Count + StartRow - 1 If Cells(j, i) = "" Then Cells(j, i).Formula = "=SUM(" & Cells(StartRow, i).Address & ":" & Cells(j - 1, i).Address & ")" StartRow = j + 1 End If Next StartRow = xRg.Row Next End Sub
3. Then press F5 key to run this code, and a prompt box is popped out to remind you selecting the range of cells that you want to sum cells above until blank cells, see screenshot:
4. Then click OK button, all cell values above blank cells have been added up as following screenshot shown: