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