| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column totals depending on different criteria
Good Morning, and a hearty Monday to all of you.
I have a worksheet with a lot of columns. In one of the columns (lets say A) I have the name of the piblications, in B I have the dates on which the ads appear, and in column C I have the cost per insertion. There is a line in between 2 different publications. The information looks something like this: Column A Column B Column C Pub 1 11th nov 500 Pub 1 12th nov 500 Pub 1 17th nov 500 Pub 1 18th nov 500 Pub 2 19th nov 300 Pub 2 24th nov 400 Pub 3 11th dec 500 Pub 3 12th dec 500 Pub 3 14th dec 500 The number of Publications vary in every file. I was wondering if someone could help with a macro to add the cost per insertion in column C, so that I get the total cost per publication in the blank row. Thanks, Prash |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Column totals depending on different criteria
You may want to try a formula the function SUMIF(range,criteria,sum column)
"Pman" wrote: Good Morning, and a hearty Monday to all of you. I have a worksheet with a lot of columns. In one of the columns (lets say A) I have the name of the piblications, in B I have the dates on which the ads appear, and in column C I have the cost per insertion. There is a line in between 2 different publications. The information looks something like this: Column A Column B Column C Pub 1 11th nov 500 Pub 1 12th nov 500 Pub 1 17th nov 500 Pub 1 18th nov 500 Pub 2 19th nov 300 Pub 2 24th nov 400 Pub 3 11th dec 500 Pub 3 12th dec 500 Pub 3 14th dec 500 The number of Publications vary in every file. I was wondering if someone could help with a macro to add the cost per insertion in column C, so that I get the total cost per publication in the blank row. Thanks, Prash |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Column totals depending on different criteria
Hi JRform,
Thanks for the reply. I'm kind of very new to VBA coding, so if you could please tell me what the formula would look like, I'd appreciate it. Thanks again, Prash. "JRForm" wrote: You may want to try a formula the function SUMIF(range,criteria,sum column) "Pman" wrote: Good Morning, and a hearty Monday to all of you. I have a worksheet with a lot of columns. In one of the columns (lets say A) I have the name of the piblications, in B I have the dates on which the ads appear, and in column C I have the cost per insertion. There is a line in between 2 different publications. The information looks something like this: Column A Column B Column C Pub 1 11th nov 500 Pub 1 12th nov 500 Pub 1 17th nov 500 Pub 1 18th nov 500 Pub 2 19th nov 300 Pub 2 24th nov 400 Pub 3 11th dec 500 Pub 3 12th dec 500 Pub 3 14th dec 500 The number of Publications vary in every file. I was wondering if someone could help with a macro to add the cost per insertion in column C, so that I get the total cost per publication in the blank row. Thanks, Prash |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Column totals depending on different criteria
Pman,
Add this code to a module and you can get your totals ALT + F11 -open VB editor add a module and paste the code below. Option Explicit Sub Pman() Dim iLastRow As Long Dim look4Me As String Dim first, sec, third As Variant iLastRow = Range("G" & Range("G:G").Rows.Count).End(xlUp).Row 'change to the column you want to search Range("G1").Select 'go to the top of the column to begin searching Do Until iLastRow = -1 If ActiveCell < "" Then look4Me = ActiveCell If ActiveCell = look4Me Then first = first + ActiveCell.Offset(0, 1) sec = sec + ActiveCell.Offset(0, 2) third = third + ActiveCell.Offset(0, 3) Else ActiveCell.Offset(0, 1) = first ActiveCell.Offset(0, 2) = sec ActiveCell.Offset(0, 3) = third 'reset for next pub first = 0 sec = 0 third = 0 End If iLastRow = iLastRow - 1 ActiveCell.Offset(1, 0).Select Loop End Sub "Pman" wrote: Hi JRform, Thanks for the reply. I'm kind of very new to VBA coding, so if you could please tell me what the formula would look like, I'd appreciate it. Thanks again, Prash. "JRForm" wrote: You may want to try a formula the function SUMIF(range,criteria,sum column) "Pman" wrote: Good Morning, and a hearty Monday to all of you. I have a worksheet with a lot of columns. In one of the columns (lets say A) I have the name of the piblications, in B I have the dates on which the ads appear, and in column C I have the cost per insertion. There is a line in between 2 different publications. The information looks something like this: Column A Column B Column C Pub 1 11th nov 500 Pub 1 12th nov 500 Pub 1 17th nov 500 Pub 1 18th nov 500 Pub 2 19th nov 300 Pub 2 24th nov 400 Pub 3 11th dec 500 Pub 3 12th dec 500 Pub 3 14th dec 500 The number of Publications vary in every file. I was wondering if someone could help with a macro to add the cost per insertion in column C, so that I get the total cost per publication in the blank row. Thanks, Prash |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Column totals depending on different criteria
Hi JRform,
I get a "type mismatch" error message when it tries to add the columns. I have a couple of columns to add, and the sample data looks like the one attached in the image below:  http://tinypic.com/view.php?pic=24vtfa1&s=2 The columns I'm trying to add up a J, L, N, P, R, T, V, X, Z, AB, AD, AF, AG and AH. I really really appreciate your help on this. Thanks, Prash "JRForm" wrote: Pman, Add this code to a module and you can get your totals ALT + F11 -open VB editor add a module and paste the code below. Option Explicit Sub Pman() Dim iLastRow As Long Dim look4Me As String Dim first, sec, third As Variant iLastRow = Range("G" & Range("G:G").Rows.Count).End(xlUp).Row 'change to the column you want to search Range("G1").Select 'go to the top of the column to begin searching Do Until iLastRow = -1 If ActiveCell < "" Then look4Me = ActiveCell If ActiveCell = look4Me Then first = first + ActiveCell.Offset(0, 1) sec = sec + ActiveCell.Offset(0, 2) third = third + ActiveCell.Offset(0, 3) Else ActiveCell.Offset(0, 1) = first ActiveCell.Offset(0, 2) = sec ActiveCell.Offset(0, 3) = third 'reset for next pub first = 0 sec = 0 third = 0 End If iLastRow = iLastRow - 1 ActiveCell.Offset(1, 0).Select Loop End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Column totals depending on different criteria
Pman,
This is different than your original question! Is your comparison data still in column A? "Pman" wrote: Hi JRform, I get a "type mismatch" error message when it tries to add the columns. I have a couple of columns to add, and the sample data looks like the one attached in the image below: http://tinypic.com/view.php?pic=24vtfa1&s=2 The columns I'm trying to add up a J, L, N, P, R, T, V, X, Z, AB, AD, AF, AG and AH. I really really appreciate your help on this. Thanks, Prash "JRForm" wrote: Pman, Add this code to a module and you can get your totals ALT + F11 -open VB editor add a module and paste the code below. Option Explicit Sub Pman() Dim iLastRow As Long Dim look4Me As String Dim first, sec, third As Variant iLastRow = Range("G" & Range("G:G").Rows.Count).End(xlUp).Row 'change to the column you want to search Range("G1").Select 'go to the top of the column to begin searching Do Until iLastRow = -1 If ActiveCell < "" Then look4Me = ActiveCell If ActiveCell = look4Me Then first = first + ActiveCell.Offset(0, 1) sec = sec + ActiveCell.Offset(0, 2) third = third + ActiveCell.Offset(0, 3) Else ActiveCell.Offset(0, 1) = first ActiveCell.Offset(0, 2) = sec ActiveCell.Offset(0, 3) = third 'reset for next pub first = 0 sec = 0 third = 0 End If iLastRow = iLastRow - 1 ActiveCell.Offset(1, 0).Select Loop End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Column totals depending on different criteria
Hi JRform,
Sorry that I messed up with the 1st question, was trying to explain in as simple a way as possible. Yes the comparison daat is still in the 1st column, i.e. the names of the publications. The software that I use does not give the option of totals when I also use the date, however it does give a total by publication by month if I exclude the date (which I cannot afford to). Hence thought that a macro would be the best bet (but I suck at it, and hence am here for help......help!). I'm sorry if I wasted your time with the 1st question again. Regards, Prash. "JRForm" wrote: Pman, This is different than your original question! Is your comparison data still in column A? "Pman" wrote: Hi JRform, I get a "type mismatch" error message when it tries to add the columns. I have a couple of columns to add, and the sample data looks like the one attached in the image below: http://tinypic.com/view.php?pic=24vtfa1&s=2 The columns I'm trying to add up a J, L, N, P, R, T, V, X, Z, AB, AD, AF, AG and AH. I really really appreciate your help on this. Thanks, Prash "JRForm" wrote: Pman, Add this code to a module and you can get your totals ALT + F11 -open VB editor add a module and paste the code below. Option Explicit Sub Pman() Dim iLastRow As Long Dim look4Me As String Dim first, sec, third As Variant iLastRow = Range("G" & Range("G:G").Rows.Count).End(xlUp).Row 'change to the column you want to search Range("G1").Select 'go to the top of the column to begin searching Do Until iLastRow = -1 If ActiveCell < "" Then look4Me = ActiveCell If ActiveCell = look4Me Then first = first + ActiveCell.Offset(0, 1) sec = sec + ActiveCell.Offset(0, 2) third = third + ActiveCell.Offset(0, 3) Else ActiveCell.Offset(0, 1) = first ActiveCell.Offset(0, 2) = sec ActiveCell.Offset(0, 3) = third 'reset for next pub first = 0 sec = 0 third = 0 End If iLastRow = iLastRow - 1 ActiveCell.Offset(1, 0).Select Loop End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Column totals depending on different criteria
Pman,
Delete the code I already posted and paste in the code here in this post. This will total the columns you posted. Sub Pman() Dim iLastRow As Long Dim look4Me As String Dim vAddingUp(14) As Variant Dim k As Integer iLastRow = Range("A" & Range("A:A").Rows.Count).End(xlUp).Row 'change to the column you want to search Range("A1").Select 'go to the top of the column to begin searching Do Until iLastRow = -1 If ActiveCell < "" Then look4Me = ActiveCell If ActiveCell = look4Me Then vAddingUp(1) = vAddingUp(1) + ActiveCell.Offset(0, 9) vAddingUp(2) = vAddingUp(2) + ActiveCell.Offset(0, 11) vAddingUp(3) = vAddingUp(3) + ActiveCell.Offset(0, 13) vAddingUp(4) = vAddingUp(4) + ActiveCell.Offset(0, 15) vAddingUp(5) = vAddingUp(5) + ActiveCell.Offset(0, 17) vAddingUp(6) = vAddingUp(6) + ActiveCell.Offset(0, 19) vAddingUp(7) = vAddingUp(7) + ActiveCell.Offset(0, 21) vAddingUp(8) = vAddingUp(8) + ActiveCell.Offset(0, 23) vAddingUp(9) = vAddingUp(9) + ActiveCell.Offset(0, 25) vAddingUp(10) = vAddingUp(10) + ActiveCell.Offset(0, 27) vAddingUp(11) = vAddingUp(11) + ActiveCell.Offset(0, 29) vAddingUp(12) = vAddingUp(12) + ActiveCell.Offset(0, 31) vAddingUp(13) = vAddingUp(13) + ActiveCell.Offset(0, 32) vAddingUp(14) = vAddingUp(14) + ActiveCell.Offset(0, 33) Else ActiveCell.Offset(0, 9) = vAddingUp(1) ActiveCell.Offset(0, 11) = vAddingUp(2) ActiveCell.Offset(0, 13) = vAddingUp(3) ActiveCell.Offset(0, 15) = vAddingUp(4) ActiveCell.Offset(0, 17) = vAddingUp(5) ActiveCell.Offset(0, 19) = vAddingUp(6) ActiveCell.Offset(0, 21) = vAddingUp(7) ActiveCell.Offset(0, 23) = vAddingUp(8) ActiveCell.Offset(0, 25) = vAddingUp(9) ActiveCell.Offset(0, 27) = vAddingUp(10) ActiveCell.Offset(0, 29) = vAddingUp(11) ActiveCell.Offset(0, 31) = vAddingUp(12) ActiveCell.Offset(0, 32) = vAddingUp(13) ActiveCell.Offset(0, 33) = vAddingUp(14) 'reset for next pub For k = 1 To 14 vAddingUp(k) = 0 Next k End If iLastRow = iLastRow - 1 ActiveCell.Offset(1, 0).Select Loop End Sub "Pman" wrote: Hi JRform, Sorry that I messed up with the 1st question, was trying to explain in as simple a way as possible. Yes the comparison daat is still in the 1st column, i.e. the names of the publications. The software that I use does not give the option of totals when I also use the date, however it does give a total by publication by month if I exclude the date (which I cannot afford to). Hence thought that a macro would be the best bet (but I suck at it, and hence am here for help......help!). I'm sorry if I wasted your time with the 1st question again. Regards, Prash. "JRForm" wrote: Pman, This is different than your original question! Is your comparison data still in column A? "Pman" wrote: Hi JRform, I get a "type mismatch" error message when it tries to add the columns. I have a couple of columns to add, and the sample data looks like the one attached in the image below: http://tinypic.com/view.php?pic=24vtfa1&s=2 The columns I'm trying to add up a J, L, N, P, R, T, V, X, Z, AB, AD, AF, AG and AH. I really really appreciate your help on this. Thanks, Prash "JRForm" wrote: Pman, Add this code to a module and you can get your totals ALT + F11 -open VB editor add a module and paste the code below. Option Explicit Sub Pman() Dim iLastRow As Long Dim look4Me As String Dim first, sec, third As Variant iLastRow = Range("G" & Range("G:G").Rows.Count).End(xlUp).Row 'change to the column you want to search Range("G1").Select 'go to the top of the column to begin searching Do Until iLastRow = -1 If ActiveCell < "" Then look4Me = ActiveCell If ActiveCell = look4Me Then first = first + ActiveCell.Offset(0, 1) sec = sec + ActiveCell.Offset(0, 2) third = third + ActiveCell.Offset(0, 3) Else ActiveCell.Offset(0, 1) = first ActiveCell.Offset(0, 2) = sec ActiveCell.Offset(0, 3) = third 'reset for next pub first = 0 sec = 0 third = 0 End If iLastRow = iLastRow - 1 ActiveCell.Offset(1, 0).Select Loop End Sub |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Column totals depending on different criteria
Thank you JrForm,
That worked like a charm :) You are a life saver :) Thanks again, Prash :) "JRForm" wrote: Pman, Delete the code I already posted and paste in the code here in this post. This will total the columns you posted. Sub Pman() Dim iLastRow As Long Dim look4Me As String Dim vAddingUp(14) As Variant Dim k As Integer iLastRow = Range("A" & Range("A:A").Rows.Count).End(xlUp).Row 'change to the column you want to search Range("A1").Select 'go to the top of the column to begin searching Do Until iLastRow = -1 If ActiveCell < "" Then look4Me = ActiveCell If ActiveCell = look4Me Then vAddingUp(1) = vAddingUp(1) + ActiveCell.Offset(0, 9) vAddingUp(2) = vAddingUp(2) + ActiveCell.Offset(0, 11) vAddingUp(3) = vAddingUp(3) + ActiveCell.Offset(0, 13) vAddingUp(4) = vAddingUp(4) + ActiveCell.Offset(0, 15) vAddingUp(5) = vAddingUp(5) + ActiveCell.Offset(0, 17) vAddingUp(6) = vAddingUp(6) + ActiveCell.Offset(0, 19) vAddingUp(7) = vAddingUp(7) + ActiveCell.Offset(0, 21) vAddingUp(8) = vAddingUp(8) + ActiveCell.Offset(0, 23) vAddingUp(9) = vAddingUp(9) + ActiveCell.Offset(0, 25) vAddingUp(10) = vAddingUp(10) + ActiveCell.Offset(0, 27) vAddingUp(11) = vAddingUp(11) + ActiveCell.Offset(0, 29) vAddingUp(12) = vAddingUp(12) + ActiveCell.Offset(0, 31) vAddingUp(13) = vAddingUp(13) + ActiveCell.Offset(0, 32) vAddingUp(14) = vAddingUp(14) + ActiveCell.Offset(0, 33) Else ActiveCell.Offset(0, 9) = vAddingUp(1) ActiveCell.Offset(0, 11) = vAddingUp(2) ActiveCell.Offset(0, 13) = vAddingUp(3) ActiveCell.Offset(0, 15) = vAddingUp(4) ActiveCell.Offset(0, 17) = vAddingUp(5) ActiveCell.Offset(0, 19) = vAddingUp(6) ActiveCell.Offset(0, 21) = vAddingUp(7) ActiveCell.Offset(0, 23) = vAddingUp(8) ActiveCell.Offset(0, 25) = vAddingUp(9) ActiveCell.Offset(0, 27) = vAddingUp(10) ActiveCell.Offset(0, 29) = vAddingUp(11) ActiveCell.Offset(0, 31) = vAddingUp(12) ActiveCell.Offset(0, 32) = vAddingUp(13) ActiveCell.Offset(0, 33) = vAddingUp(14) 'reset for next pub For k = 1 To 14 vAddingUp(k) = 0 Next k End If iLastRow = iLastRow - 1 ActiveCell.Offset(1, 0).Select Loop End Sub |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Column totals depending on different criteria
you welcome.
"Pman" wrote: Thank you JrForm, That worked like a charm :) You are a life saver :) Thanks again, Prash :) "JRForm" wrote: Pman, Delete the code I already posted and paste in the code here in this post. This will total the columns you posted. Sub Pman() Dim iLastRow As Long Dim look4Me As String Dim vAddingUp(14) As Variant Dim k As Integer iLastRow = Range("A" & Range("A:A").Rows.Count).End(xlUp).Row 'change to the column you want to search Range("A1").Select 'go to the top of the column to begin searching Do Until iLastRow = -1 If ActiveCell < "" Then look4Me = ActiveCell If ActiveCell = look4Me Then vAddingUp(1) = vAddingUp(1) + ActiveCell.Offset(0, 9) vAddingUp(2) = vAddingUp(2) + ActiveCell.Offset(0, 11) vAddingUp(3) = vAddingUp(3) + ActiveCell.Offset(0, 13) vAddingUp(4) = vAddingUp(4) + ActiveCell.Offset(0, 15) vAddingUp(5) = vAddingUp(5) + ActiveCell.Offset(0, 17) vAddingUp(6) = vAddingUp(6) + ActiveCell.Offset(0, 19) vAddingUp(7) = vAddingUp(7) + ActiveCell.Offset(0, 21) vAddingUp(8) = vAddingUp(8) + ActiveCell.Offset(0, 23) vAddingUp(9) = vAddingUp(9) + ActiveCell.Offset(0, 25) vAddingUp(10) = vAddingUp(10) + ActiveCell.Offset(0, 27) vAddingUp(11) = vAddingUp(11) + ActiveCell.Offset(0, 29) vAddingUp(12) = vAddingUp(12) + ActiveCell.Offset(0, 31) vAddingUp(13) = vAddingUp(13) + ActiveCell.Offset(0, 32) vAddingUp(14) = vAddingUp(14) + ActiveCell.Offset(0, 33) Else ActiveCell.Offset(0, 9) = vAddingUp(1) ActiveCell.Offset(0, 11) = vAddingUp(2) ActiveCell.Offset(0, 13) = vAddingUp(3) ActiveCell.Offset(0, 15) = vAddingUp(4) ActiveCell.Offset(0, 17) = vAddingUp(5) ActiveCell.Offset(0, 19) = vAddingUp(6) ActiveCell.Offset(0, 21) = vAddingUp(7) ActiveCell.Offset(0, 23) = vAddingUp(8) ActiveCell.Offset(0, 25) = vAddingUp(9) ActiveCell.Offset(0, 27) = vAddingUp(10) ActiveCell.Offset(0, 29) = vAddingUp(11) ActiveCell.Offset(0, 31) = vAddingUp(12) ActiveCell.Offset(0, 32) = vAddingUp(13) ActiveCell.Offset(0, 33) = vAddingUp(14) 'reset for next pub For k = 1 To 14 vAddingUp(k) = 0 Next k End If iLastRow = iLastRow - 1 ActiveCell.Offset(1, 0).Select Loop End Sub |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Column totals depending on different criteria
Just one more help JR,
Is it possible to past-special the totals that are derived from your macro, and shift it one cell to the left? My client wants the totals to come below the dates in the date columns and delete the cost columns (please refer the image I posted above) :( Please please please do help me out. Thanks, Prash "JRForm" wrote: you welcome. "Pman" wrote: Thank you JrForm, That worked like a charm :) You are a life saver :) Thanks again, Prash :) "JRForm" wrote: Pman, Delete the code I already posted and paste in the code here in this post. This will total the columns you posted. Sub Pman() Dim iLastRow As Long Dim look4Me As String Dim vAddingUp(14) As Variant Dim k As Integer iLastRow = Range("A" & Range("A:A").Rows.Count).End(xlUp).Row 'change to the column you want to search Range("A1").Select 'go to the top of the column to begin searching Do Until iLastRow = -1 If ActiveCell < "" Then look4Me = ActiveCell If ActiveCell = look4Me Then vAddingUp(1) = vAddingUp(1) + ActiveCell.Offset(0, 9) vAddingUp(2) = vAddingUp(2) + ActiveCell.Offset(0, 11) vAddingUp(3) = vAddingUp(3) + ActiveCell.Offset(0, 13) vAddingUp(4) = vAddingUp(4) + ActiveCell.Offset(0, 15) vAddingUp(5) = vAddingUp(5) + ActiveCell.Offset(0, 17) vAddingUp(6) = vAddingUp(6) + ActiveCell.Offset(0, 19) vAddingUp(7) = vAddingUp(7) + ActiveCell.Offset(0, 21) vAddingUp(8) = vAddingUp(8) + ActiveCell.Offset(0, 23) vAddingUp(9) = vAddingUp(9) + ActiveCell.Offset(0, 25) vAddingUp(10) = vAddingUp(10) + ActiveCell.Offset(0, 27) vAddingUp(11) = vAddingUp(11) + ActiveCell.Offset(0, 29) vAddingUp(12) = vAddingUp(12) + ActiveCell.Offset(0, 31) vAddingUp(13) = vAddingUp(13) + ActiveCell.Offset(0, 32) vAddingUp(14) = vAddingUp(14) + ActiveCell.Offset(0, 33) Else ActiveCell.Offset(0, 9) = vAddingUp(1) ActiveCell.Offset(0, 11) = vAddingUp(2) ActiveCell.Offset(0, 13) = vAddingUp(3) ActiveCell.Offset(0, 15) = vAddingUp(4) ActiveCell.Offset(0, 17) = vAddingUp(5) ActiveCell.Offset(0, 19) = vAddingUp(6) ActiveCell.Offset(0, 21) = vAddingUp(7) ActiveCell.Offset(0, 23) = vAddingUp(8) ActiveCell.Offset(0, 25) = vAddingUp(9) ActiveCell.Offset(0, 27) = vAddingUp(10) ActiveCell.Offset(0, 29) = vAddingUp(11) ActiveCell.Offset(0, 31) = vAddingUp(12) ActiveCell.Offset(0, 32) = vAddingUp(13) ActiveCell.Offset(0, 33) = vAddingUp(14) 'reset for next pub For k = 1 To 14 vAddingUp(k) = 0 Next k End If iLastRow = iLastRow - 1 ActiveCell.Offset(1, 0).Select Loop End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count/Sum rows of a column depending on criteria from drop-down li | Excel Worksheet Functions | |||
| Charting depending on criteria & data series name as a column val | Charts and Charting in Excel | |||
| I want to add totals using several criteria from different column | Excel Discussion (Misc queries) | |||
| How do I add totals from a range of dates depending on the month? | Excel Worksheet Functions | |||
| Calculating totals depending on cell value | Excel Programming | |||
Linear Mode
