VBA CountIfs with multiple criteria from same range

[Lres81715]

Good morning/afternoon all

I'm trying to figure out how to use CountIfs for a monthly Management report strictly using VBA. I can do it using arrays in excel formulas, but the language barrier from Formula to VBA is just beyond me at the present.
Here's what I got so far.

Dim ws1         As Worksheet
Dim rg           As Worksheet
Dim LRColA      As Long
Dim LastRow     As Long

    Set ws1 = Sheets("Management Report")
    Set rg = Sheets("RG-ALLDATA")

    LRColA = Range("A:A").Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row


     ws1.Range("G" & LRColA + 2) = Application.CountIfs(rg.Range("AJ1:AJ" & aLastRow), ws1.Range("B" & LRColA + 2), _
                            rg.Range("AC1:AC" & aLastRow), "U" Or "N" Or "R" Or "L" Or "P" Or "O")

For context, the macro above first finds the last entry made on Column A, From there, it determines the contents of what's in Column B 2 rows below it. Then it uses that as the criteria for my CountIf statement.

This works when I have multiple criteria with different Range. But now I need to count the same range AC1 through end row AC. The contents have 17 different "statuses". I only want to count if the status is "U,N,R,L,P,O" not the rest. I'm stumped...

any help in the right direction would be appreciated.

(Edit: I should probably say that I know I can just add the countifs together using countif statement and "U" + countif statement and "N" + countif statement and "R" Etc. But that's extremely clunky. I'd rather find an easier, less wordy version of doing this)

 

[pgc01]

Hi

If I understand correctly you need something like:

Sub Test()
Dim r As Range

Set r = Range("A1", Range("A" & Rows.Count).End(xlUp))

MsgBox Application.Sum(Application.CountIf(r, Array("A", "P", "U")))

End Sub

 

[Lres81715]

application.sum and Array(""),

That's what I was missing.
Thanks!

Edit: Works perfect now

 

[pgc01]

You're welcome. Thanks for the feedback.