=COUNTIF(RANGE, CONDITION) Example: =COUNTIF(C1:C30, "Paris")
=SUM(--(MMULT(--(ISNUMBER(SEARCH(G4,data))),TRANSPOSE(COLUMN(data)^0))>0))
Application.WorksheetFunction.Subtotal(3, Range("A2:A500000"))