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
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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: