Sum data range if a cell in the column is blank

AccountantHarry

New Member
Joined
Oct 13, 2019
Messages
18
Hi

I have a long list of data with 10 groups, I need to sum each group when the column next to it is blank. It works fine to me when a pivot table is used but some of the sales guys don't like it. I wonder if there is a formula or macro code I can use. An example of the data range is as follows:

Biz owner Sales Contribution ………….
1 100 30
1 120 60

2 50 10
2 40 20

so on and so forth...

I need to sum Sales and Contribution for the Biz owner group when the Biz owner column is blank.

Thanks in advance
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Where would you like to put the sum ?
 
Upvote 0
Is it what you need ??
Code:
Option Explicit


Sub Treat()
Dim WkRg  As Range
Dim WkAr
Dim LC  As Integer, LR  As Integer
Const DispAdd = "$H$4"
Dim I  As Integer
    LC = Cells(1, Columns.Count).End(xlToLeft).Column
    LR = Cells(Rows.Count, 1).End(3).Row
    On Error Resume Next
    Range(DispAdd).CurrentRegion.ClearContents
    On Error GoTo 0
    Range(DispAdd).Cells(1, 1).Resize(1, 2) = Array("Biz owner", "Sales")


    Set WkRg = Range(Cells(1, 1), Cells(Rows.Count, 1).End(3))
    Set WkRg = WkRg.SpecialCells(xlCellTypeConstants)
    I = 1
    For Each WkAr In WkRg.areas
        With WkAr
            Range(DispAdd)(1 + I, 1) = .Cells(.Rows.Count, 1)
            Range(DispAdd)(1 + I, 2) = Application.Sum(.Columns(2).Cells)
            I = I + 1
        End With
    Next WkAr
End Sub
 
Upvote 0
HI

Column A is Biz Owner, Column B is Sales Contribution, Column C is Contribution, the sum needs to be in Column B and also Column C when the cell in column A is blank
 
Upvote 0
Last revision
Code:
Option Explicit


Sub Treat()
Dim WkRg  As Range
Dim WkAr
Dim LR  As Integer
Dim I  As Integer
    LR = Cells(Rows.Count, 1).End(3).Row            '  Last Row


    Set WkRg = Range(Cells(1, 1), Cells(Rows.Count, 1).End(3))
    Set WkRg = WkRg.SpecialCells(xlCellTypeConstants)   '  All  area where ther is data in column A
    I = 1
    For Each WkAr In WkRg.areas
        With WkAr           '  For each area where there is data in column A
            .Cells(.Rows.Count + 1, 2) = Application.Sum(.Columns(2).Cells) ' make sum of  column 2 of the area & put it at the end
            .Cells(.Rows.Count + 1, 3) = Application.Sum(.Columns(3).Cells) ' make sum of  column 3 of the area &  put it at the end
            I = I + 1
        End With
    Next WkAr
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top