Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF with sections of data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF with sections of data
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)? -- Rick (MVP - Excel) "Brian" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF with sections of data
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" wrote: 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)? -- Rick (MVP - Excel) "Brian" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF with sections of data
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 -- Rick (MVP - Excel) "Brian" wrote in message ... 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" wrote: 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)? -- Rick (MVP - Excel) "Brian" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF with sections of data
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" wrote: 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 -- Rick (MVP - Excel) "Brian" wrote in message ... 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" wrote: 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)? -- Rick (MVP - Excel) "Brian" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF with sections of data
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. -- Rick (MVP - Excel) "Brian" wrote in message ... 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" wrote: 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 -- Rick (MVP - Excel) "Brian" wrote in message ... 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" wrote: 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)? -- Rick (MVP - Excel) "Brian" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF with sections of data
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" wrote: 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. -- Rick (MVP - Excel) "Brian" wrote in message ... 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" wrote: 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 -- Rick (MVP - Excel) "Brian" wrote in message ... 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" wrote: 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)? -- Rick (MVP - Excel) "Brian" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF with sections of data
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 -- Rick (MVP - Excel) "Brian" wrote in message ... 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" wrote: 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. -- Rick (MVP - Excel) "Brian" wrote in message ... 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" wrote: 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 -- Rick (MVP - Excel) "Brian" wrote in message ... 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" wrote: 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)? -- Rick (MVP - Excel) "Brian" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF with sections of data
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. "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "Brian" wrote in message ... 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" wrote: 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. -- Rick (MVP - Excel) "Brian" wrote in message ... 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" wrote: 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 -- Rick (MVP - Excel) "Brian" wrote in message ... 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" wrote: 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)? -- Rick (MVP - Excel) "Brian" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging Sections of data | Excel Worksheet Functions | |||
Copy Paste sections of data | Excel Worksheet Functions | |||
Worksheet Sections | Excel Discussion (Misc queries) | |||
if formula with two sections | Excel Discussion (Misc queries) | |||
Delte blank rows between different sections of data | Excel Programming |