Count if filtered data with criteria by Excel functions In a blank cell enter the formula =PRODUCT(SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),1)), ISNUMBER(SEARCH(“Pear”,B2:B18))+0), and press the Enter key.