Using SumIF with an array
 

I export from Access to text and then import into Excel, the export from
Access truncates some ending characters. To offset this I have to sum some
records once I get it to Excel but am having trouble with the following sumif

If the value in column D is a Month (January, February etc) then sum only
those rows in column Q. I have figured it out as an Excel formula but how is
this converted to vb?

Using SumIF with an array
 

It would have been helpful if you posted your formula... then I wouldn't
have to ask you exactly what is in Column D... the month name as a spelled
out text word or the number of the month?

--
Rick (MVP - Excel)


"jeremiah" wrote in message
...
I export from Access to text and then import into Excel, the export from
Access truncates some ending characters. To offset this I have to sum
some
records once I get it to Excel but am having trouble with the following
sumif

If the value in column D is a Month (January, February etc) then sum only
those rows in column Q. I have figured it out as an Excel formula but how
is
this converted to vb?

Using SumIF with an array
 

Sorry, the formula is below. I need to be able to do this in a worksheet
that will vary in number of rows which is why I am using vb - I figured this
out by identifying a range but will adjust it to use with an array of January
through December eventually. I know I will have to use the lastrow function
but was trying to figure this part out first.

=SUMIF(D1:D20,"January",Q1:Q20)+SUMIF(D1:D20,"Febr uary",Q1:Q20)


"Rick Rothstein" wrote:

It would have been helpful if you posted your formula... then I wouldn't
have to ask you exactly what is in Column D... the month name as a spelled
out text word or the number of the month?

--
Rick (MVP - Excel)


"jeremiah" wrote in message
...
I export from Access to text and then import into Excel, the export from
Access truncates some ending characters. To offset this I have to sum
some
records once I get it to Excel but am having trouble with the following
sumif

If the value in column D is a Month (January, February etc) then sum only
those rows in column Q. I have figured it out as an Excel formula but how
is
this converted to vb?

Using SumIF with an array
 

I have the following working...is there a way to use an array rather than
having to add new code for each month?

Sub OffsetSumTestDecks()
Dim Cell As Range
Sub OffsetSumTestDecks()
Dim Cell As Range
' Macro8 Macro
' Macro recorded 1/13/2009 by PEDCS Workstation

For Each Cell In Range("d:d")
If Cell.Value = "Year To Date" Then
Cell.Offset(0, 13) =
"=SUMIF(R[-14]C[-13]:R[5]C[-13],""January"",R[-14]C:R[5]C)+SUMIF(R[-14]C[-13]:R[5]C[-13],""February"",R[-14]C:R[5]C)+SUMIF(R[-14]C[-13]:R[5]C[-13],""March"",R[-14]C:R[5]C)"
End If
Next Cell



"jeremiah" wrote:

Sorry, the formula is below. I need to be able to do this in a worksheet
that will vary in number of rows which is why I am using vb - I figured this
out by identifying a range but will adjust it to use with an array of January
through December eventually. I know I will have to use the lastrow function
but was trying to figure this part out first.

=SUMIF(D1:D20,"January",Q1:Q20)+SUMIF(D1:D20,"Febr uary",Q1:Q20)


"Rick Rothstein" wrote:

It would have been helpful if you posted your formula... then I wouldn't
have to ask you exactly what is in Column D... the month name as a spelled
out text word or the number of the month?

--
Rick (MVP - Excel)


"jeremiah" wrote in message
...
I export from Access to text and then import into Excel, the export from
Access truncates some ending characters. To offset this I have to sum
some
records once I get it to Excel but am having trouble with the following
sumif

If the value in column D is a Month (January, February etc) then sum only
those rows in column Q. I have figured it out as an Excel formula but how
is
this converted to vb?