SUMIF with sections of data

[Brian]

I have 2 columns of data and each section is separated by a blank line. Each
section doesn't have the same amount of rows. Is there some way for a user to
hit a button (macro?) that will sum each section?

PRODUCT A $1
PRODUCT A $100
PRODUCT B $50
PRODUCT B $20

PRODUCT A $5000
PRODUCT A $6000
PRODUCT B $50
PRODUCT B $20
PRODUCT B $10
PRODUCT B $2

 

[Rick Rothstein]

In order to avoid your having to come back and ask "How do I modify your
code for this...?", tell us what columns (letters) your two columns are,
what row number is your first piece of data on, and tell us where you want
the sum to go (under the amounts, in a column next to it, on some other
worksheet in some other column). Also, did you want the total for all
products or for each individual product (within each grouping, of course)?

 

[Brian]

Data starts in A1, dollar amount in Column B.
would like to have the sum in the cell under the product column. I only need
the sum for Product A.

 

[Rick Rothstein]

This macro should do what you want (just change the "Sheet1" reference to
the worksheet name where you want to apply the macro to)...

Sub SumProductAbySections()
Dim X As Long, Total As Long, FirstRow As Long, LastRow As Long
With Worksheets("Sheet1")
FirstRow = .Columns("A").Find("*", After:=Cells(.Rows.Count, "A")).Row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
For X = FirstRow To LastRow
If Len(.Cells(X, "B").Value) = 0 Then
.Cells(X, "A").Value = Total
Total = 0
ElseIf UCase(.Cells(X, "A")) = "PRODUCT A" Then
Total = Total + .Cells(X, "B").Value
End If
Next
End With
End Sub

 

[Brian]

This is perfect! What about this modification...
I see the sums of Product A in the cell that I want, how would I put the
value of Product B next to it? I already copied the VB Module and entered
"Product B" and that worked. So now I have 2 modules where we can choose what
we want to sum (either Product A or B) but I want to plan when they want to
do both and have both sums side by side (or just in different cells).
Thanks for your help!

 

[Rick Rothstein]

In my code below, the marked line controls where the output goes...

Sub SumProductAbySections()
Dim X As Long, Total As Long, FirstRow As Long, LastRow As Long
With Worksheets("Sheet1")
FirstRow = .Columns("A").Find("*", After:=Cells(.Rows.Count, "A")).Row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
For X = FirstRow To LastRow
If Len(.Cells(X, "B").Value) = 0 Then
==> .Cells(X, "A").Value = Total
Total = 0
ElseIf UCase(.Cells(X, "A")) = "PRODUCT A" Then
Total = Total + .Cells(X, "B").Value
End If
Next
End With
End Sub

Just change the "A" to whichever column you want to the Total to go to.

 

[Brian]

One last thing and that should be it
How do I sum the total $$ regardless of Product? I would have the sum
entered below in the blank row. Then move down (or up) the sheet and add the
next group of data. Each group of data is separated by a blank line.

 

[Rick Rothstein]

Okay, I've generalized my code to make it easier for you to change in the
future if necessary. The controlling parameters (product name and output
column) are now specified in two Const (the VB keyword for "constant")
statements... the names should tell you what to assign to each. I also
change the data type for the Total (and, now, GrandTotal also) to Double
from the Long I had originally declared them as. This will not affect the
output for the example data you posted, but will allow these two totals to
track pennies as well as dollars if necessary. I also corrected a minor flaw
in how I checked the product names so that now the product names do not have
to all be listed in the same letter casing. The grand totals for the product
being added up in each section is shown 2 rows below the last total in the
specified column (I thought the blank row made the display less confusing).

Sub SumProductbySectionsWithGrandTotal()
Dim Total As Double, GrandTotal As Double
Dim X As Long, FirstRow As Long, LastRow As Long
'
Const OutputColumn As String = "A"
Const ProductName As String = "PRODUCT A"
'
With Worksheets("Sheet1")
FirstRow = .Columns(OutputColumn).Find("*", After:=Cells( _
.Rows.Count, OutputColumn)).Row
LastRow = .Cells(.Rows.Count, OutputColumn).End(xlUp).Row + 1
For X = FirstRow To LastRow
If Len(.Cells(X, "B").Value) = 0 Then
.Cells(X, OutputColumn).Value = Total
GrandTotal = GrandTotal + Total
Total = 0
ElseIf UCase(.Cells(X, "A")) = UCase(ProductName) Then
Total = Total + .Cells(X, "B").Value
End If
Next
.Cells(LastRow + 2, OutputColumn).Value = GrandTotal
End With
End Sub

 

[Brian]

Great! I see the grand total at the bottom of the page. How can I edit this
so I have a grand total for each section rather than the entire sheet? So
each section of data will sum up the total for both Product A and B.