Using SumIF with an array

J

jeremiah

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

Rick Rothstein

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

jeremiah

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,"February",Q1:Q20)
 
J

jeremiah

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top