Search code examples
vbaexcelexcel-2013

Excel VBA CountIF To Search For String Array

How can I use the COUNTIF() function to count only certain text strings that exist in the range?

I tried to use the below, but I get an error of

Syntax error

This is the syntax I attempted

Dim worksheetmaster As String = "Master"
Dim worksheettocheck As String = "New"
Dim softcount As Int, i As Long, hardcount As Int

softcount = Evaluate("=COUNTIF(Range('" & worksheettocheck & "'!A:A'" & worksheetmaster & "'!A" & i & ")"Soft")")
hardcount = Evaluate("=COUNTIF(Range('" & worksheettocheck & "'!A:A'" & worksheetmaster & "'!A" & i & ")"Hard")")

EDIT
I tried to use this syntax without the Range and am still getting the error

hardcount = Evaluate("=COUNTIF('" & worksheettocheck & "'!A:A'" & worksheetmaster & "'!A" & i & ")"Hard"")
Solution

  • To match in column A with Hard in column B, this is how it should be:

    hardcount = Application.Evaluate("COUNTIFS('" & worksheettocheck & "'!A:A,'" & worksheetmaster & "'!A" & i & ",'"  & worksheettocheck & "'!B:B, ""Hard"")")

softcount = Application.Evaluate("COUNTIFS('" & worksheettocheck & "'!A:A,'" & worksheetmaster & "'!A" & i & ",'"  & worksheettocheck & "'!B:B, ""Soft"")")