Search code examples
excelcriteriacountif

Excel - Count multiple occurances of a value based on a critera

I am trying to count occurances of an account number appearing in column A only when the value in column B = "abc".

COUNTIFS(data!G2:N173529,A2) returns the count where the account number appears. However, it returns the complete count without filtering based on column B. How can I add another criteria to further simplify the count based on column B.

UPDATE: (COUNTIF($C$2:$C$173529,A2)>1,"Multiple Matches",VLOOKUP(A2,C:M,7,FALSE)) This returns value from column 7 where A2 value appears once in column C and returns "Multiple Matches" where A2 appears more than once in column C.

However, how do I add the second criteria to check if text "abc" appears in column B.

Solution

  • Use countifs

    =COUNTIFS(data!G:N,A2,B:B,"abc")

    example:

    check   check   "abc"   
        heck    "abc"   
        speck   "abc"   
        check   " abc"  
        check   "abc"   
        check   "1abc"

    I put =countifs(B:B,A1,C:C,"abc")

    which correctly returned 2