Sumproduct & Dates

[catlair]

Hi,

Am trying to get my little macro to search for a particular month in a
particular year and match it with a criteria before providing a total.

For example, am searching under column A for "Jun=06" then matching it
with "S/W" before summing up with the total figure.

My macro:

Dim iDataRows As Long
iDataRows = WorksheetFunction.CountA(Range("A:A"))

Dim iAddLines As Long
iAddLines = iDataRows
BanRangeA = iAddLines + 5

Range("A" & BanRangeA).Select
ActiveCell.Formula = "=SUMPRODUCT(--(DATE(" & "A2:A" & iDataRows &
"=""Jun-06"")),--(" & "F2:F" & iDataRows & "=""S/W""))"

Please advise. Thanks!

 

[JE McGimpsey]

Dim iDataRows As Long
iDataRows = WorksheetFunction.CountA(Range("A:A"))

Dim iAddLines As Long
iAddLines = iDataRows
BanRangeA = iAddLines + 5

Range("A" & BanRangeA).Select
ActiveCell.Formula = "=SUMPRODUCT(--(DATE(" & "A2:A" & iDataRows &
"=""Jun-06"")),--(" & "F2:F" & iDataRows & "=""S/W""))"

One way, if I'm inferring your data layout correctly:

Const sTEMPLATE As String = _
"=SUMPRODUCT(--(MONTH($$)=6),--(YEAR($$)=2006),--(##=""S/W""))"

With ActiveSheet.Range("A2").Resize( _
Application.CountA(Range("A:A")) - 1, 1)
.Cells(.Count).Offset(5, 0).Formula = _
Application.Substitute(Application.Substitute( _
sTEMPLATE, "$$", .Address(False, False)), _
"##", .Offset(, 5).Address(False, False))
End With

this will give you a count of rows for which A2:An is June 2006 AND the
corresponding row in F2:Fn is "S/W"

Note that no selection is necessary if you work directly with the range
objects. This is always more faster, generally leads to smaller code,
and, once one gets used to it, easier to maintain.

 

[catlair]

Tried it and the result is still "0". ???

regards,

 

[JE McGimpsey]

Tried it and the result is still "0". ???

Still??

What is the result when you enter the formula manually?

Are your dates actually dates? or are they text?

Do you have rows for which the date is in June 2006 and the
corresponding cell in column F is the text "S/W"?

The code I provided assumes that column A contains dates (as per your
subject line). If your "dates" are text, instead (i.e., 'Jun-06), then
using this template


Const sTEMPLATE As String = _
"=SUMPRODUCT(--($$=""Jun-06""),--(##=""S/W""))"


should work, as long as the text matches exactly.

 

[catlair]

Hi,

It works!

Not sure what you mean by text. The data is provided to me on a regular
basis so I guess it is in a text format if that is what you mean.

catlair

 

[JE McGimpsey]

Not sure what you mean by text. The data is provided to me on a regular
basis so I guess it is in a text format if that is what you mean.

XL stores dates as integers offset from a base date (1/1/1904 for the
MacXL default date system), even if it displays them in date format.

Your data apparently does not consist of actual dates (integer values),
but instead constists of Text strings such as "Jun-06".

 

[catlair]

Hi,

Strangely though the original data shows the text data as "2006-06" I
had converted it by manually changing it to "6/1/2006" though it is set
to reflect "Jun-06". ???

Thanks much!

catlair

 

[catlair]

Hi,

Strangely, the original data shows the text data as "2006-06". I
converted it by manually changing it to "6/1/2006" and it was
configured to reflect "Jun-06". ???

Now, if I wish to include a search for "H/W", do I repeat the same
command lines as per following:

Const sTEMPLATE As String = _
"=SUMPRODUCT(--($$=""2006-06""),--(##=""S/W""))"

With ActiveSheet.Range("A2").Resize( _
Application.CountA(Range("A:A")) - 1, 1)
.Cells(.Count).Offset(5, 0).Formula = _
Application.Substitute(Application.Substitute( _
sTEMPLATE, "$$", .Address(False, False)), _
"##", .Offset(, 5).Address(False, False))
End With

Const sHW As String = _
"=SUMPRODUCT(--($$=""2006-06""),--(##=""H/W""))"

With ActiveSheet.Range("A2").Resize( _
Application.CountA(Range("A:A")) - 1, 1)
.Cells(.Count).Offset(5, 0).Formula = _
Application.Substitute(Application.Substitute( _
sHW, "$$", .Address(False, False)), _
"##", .Offset(, 5).Address(False, False))
End With

And how can I position the second result next to the first and not
below it?

catlair

 

[JE McGimpsey]

And how can I position the second result next to the first and not
below it?

I'd probably do something like

Const sTEMPLATE As String = _
"=SUMPRODUCT(--($$=""2006-06""),--(##=""^^""))"
Const sS1 = "S/W"
Const sS2 = "H/W"
Dim sTemp As String

With ActiveSheet.Range("A2").Resize( _
Application.CountA(Range("A:A")) - 1, 1)
sTemp = Application.Substitute(Application.Substitute( _
sTEMPLATE, "$$", .Address(False, False)), _
"##", .Offset(, 5).Address(False, False))
With .Cells(.Count).Offset(5, 0).Resize(1, 2)
.Cells(1).Formula = Application.Substitute(sTemp, "^^", sS1)
.Cells(2).Formula = Application.Substitute(sTemp, "^^", sS2)
End With
End With

 

[catlair]

Hi,

The original data shows the text data as "2006-06". I converted it by
manually changing it to "6/1/2006" and it was configured to reflect
"Jun-06". ???

I have added two additional search criteria and position the results
side by side:

Const sTEMPLATE As String = _
"=SUMPRODUCT(--($$=""2006-06""),--(##=""S/W""))"
Const sHW As String = _
"=SUMPRODUCT(--($$=""2006-06""),--(##=""H/W""))"
Const sConsult As String = _
"=SUMPRODUCT(--($$=""2006-06""),--(##=""Consult""))"

With ActiveSheet.Range("A2").Resize( _
Application.CountA(Range("A:A")) - 1, 1)
.Cells(.Count).Offset(5, 0).Formula = _
Application.Substitute(Application.Substitute( _
sTEMPLATE, "$$", .Address(False, False)), _
"##", .Offset(, 5).Address(False, False))
.Cells(.Count).Offset(5, 1).Formula = _
Application.Substitute(Application.Substitute( _
sHW, "$$", .Address(False, False)), _
"##", .Offset(, 5).Address(False, False))
.Cells(.Count).Offset(5, 2).Formula = _
Application.Substitute(Application.Substitute( _
sConsult, "$$", .Address(False, False)), _
"##", .Offset(, 5).Address(False, False))
End With

Is this efficient?

And, how can I have the search begin with 2006-06 and end with this
month?

For example,

Date S/W H/W Consulting
------- ---- ----- ---------
2006-06 2 4 8
2006-07 1 3 9

 

[catlair]

And, how can I have the search begin with 2006-06 and end with the
current month and year?

For example,

Date S/W H/W
------- ---- -----
2006-06 2 4
2006-07 1 3