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 |
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 |