Sum data range if a cell in the column is blank

[AccountantHarry]

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

 

[PCL]

Where would you like to put the sum ?

 

[alansidman]

Maybe Sub totals function

https://www.dummies.com/software/mi...-insert-subtotals-in-an-excel-2010-worksheet/

 

[PCL]

Is it what you need ??

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

 

[AccountantHarry]

Thanks. Subtotal won't work, the sales guys only want to see a straight forward flat data range

Maybe Sub totals function

https://www.dummies.com/software/mi...-insert-subtotals-in-an-excel-2010-worksheet/

 

[AccountantHarry]

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

 

[AccountantHarry]

Hi
Thanks. Can you explain the codes please?

 

[PCL]

Last revision

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