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

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

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

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

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

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

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

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

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

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

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