Combine countifs & Subtotal

[av8tordude]

how can i combine this two codes?





WorksheetFunction.CountIfs(Range("A7:A" & LRow), "<>0", Range("K7:K" & LRow), ">0")

WorksheetFunction.Subtotal(9, Range("A7:A" & LRow))

[av8tordude]

I did some google research and I tried this code, but it flags an error on countifs. Please help. thanks


WorksheetFunction.Subtotal(9, CountIfs(Range("A7:A" & LRow), "<>0", Range("K7:K" & LRow), ">0"))

[paulked]

Subtotal of what?

[av8tordude]

There are 2 "BA" listed column A. In column K indicate the status of each position. If a date is listed, it indicates the positions is closed. Only one "BA" indicates the position is closed. I would like to count the number of open positions in T12 and the number of closed positions in T13 when I filter the sheet.

[paulked]

You don't need subtotal for that.



lr = Cells(Rows.Count, 1).End(3).Row
Range("t12") = WorksheetFunction.CountIfs(Range("a7:a" & lr), "BA", Range("k7:k" & lr), "")
Range("t13") = WorksheetFunction.CountIfs(Range("a7:a" & lr), "BA", Range("k7:k" & lr), "<>")

[av8tordude]

Thank you very much Paulked. Works great.