VBA Countif help Passing Criteria through Array

[malleshg24]

Hi Team,
Need your help for making countif formula dynamic.Criteria will be from Range("E2:E" & LastRow)
Range is Column A Which Contains products list.

Below line works.
MsgBox Application.Sum(Application.CountIf(r, Array("FX Option", "Forward")))

This line is not working.
'MsgBox Application.Sum(Application.CountIf(r, Array(" & arr & "))


Below are my attempted Code.
Sub Test()
Dim r As Range
Dim lastrow As Long
Dim arr As Variant

'arr = Array("FX Option", "Forward")
lastrow = Range("e1000").End(xlUp).Row


arr = Range("E2:E" & lastrow).Value
Set r = Sheet1.Range("A1", Range("A" & Rows.Count).End(xlUp))


'MsgBox Application.Sum(Application.CountIf(r, Array(" & arr & "))
MsgBox Application.Sum(Application.CountIf(r, Array("FX Option", "Forward")))

End Sub


Plz help, Thanks in advance for your precious time.




Regards,
mg

[akuini]

arr becomes an array when this line is executed:

arr = Range("E2:E" & lastrow).Value

So, try:

MsgBox Application.Sum(Application.CountIf(r, arr))