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 up to the 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
Sum cell values below until blank cells with array formula
Sum cell values above until blank cells with VBA code
Sum cell values below until blank cells with array formula
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:
Sum cell values above until blank cells with VBA code
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
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:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite ā Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license ā set up in minutes (MSI-ready)
- Works better together ā streamlined productivity across Office apps
- 30-day full-featured trial ā no registration, no credit card
- Best value ā save vs buying individual add-in