VBA CountIfs with Multiple Criteria

[pinzhi]

This is my first post in Mr. Excel. Thank you guys in advance for any guidance you can provide me.

I am trying to convert the following Excel formula below to a VBA macro. Essentially, I only want to count the cell if the date is 10/23/2015 in column L and the text in column K says T.

=COUNTIFS('Source Data'!$L:$L,"10/23/2015",'Source Data'!$K:$K,"T")

I have tried playing around with the following macro for the past hour but can't get it to work ....please HELP!

'Calculate Done Count for 10/23/2015
Sub Macro5()


Dim CountIfX As Long
Dim Requested As Range
Dim Touched As Range
Dim ReqDate As String
Dim T As String




Set Touched = Sheet1.Range("K2:K5228")
Set Requested = Sheet1.Range("L2:L5228")
ReqDate = "10/23/2015"
T = "T"
CountIfX = Evaluate("=SUMPRODUCT((Requested = " & ReqDate & ")*(Touched = " & T & "))")

Sheets("Summary").Range("C8").Value = CountIfX
End Sub

 

[LockeGarmin]

If you want to just be able to put your formula in VBA without a lot of hassle you could use the following code: (notice how strings are double quoted)

Sub CountIfsFormula()

Dim wsSummary As Worksheet
Dim sFormula As String

  Set wsSummary = Sheets("Summary")
  sFormula = "=COUNTIFS('Source Data'!$L:$L,""10/23/2015"",'Source Data'!$K:$K,""T"")"
  
  With wsSummary.Range("C8")
    
    'Insert Formula
    .Formula = sFormula
    
    'Ensure calculation happens
    wsSummary.Calculate
    
    'Convert Formula to Value
    .Value2 = .Value2
    
  End With

End Sub

If you want to go a purely VBA route you could use the following: (Notice the date in # signs instead of quotes, this is how you format dates in VBA)

Sub CountIfsFormula()

Dim wsSourceData As Worksheet

  Set wsSourceData = Worksheets("Source Data")

  Sheets("Summary").Range("C8").Value2 = _
    Excel.WorksheetFunction.CountIfs( _
    wsSourceData.[L:L], #10/23/2015#, wsSourceData.[K:K], "T")

End Sub