0

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.

Sarah
  • 1,895
  • 2
  • 21
  • 39

1 Answers1

2

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

David Andrei Ned
  • 799
  • 1
  • 11
  • 28
  • not working. I tried the following: `IF(COUNTIF($C$2:$C$173529,A2)>1,"Multiple Matches",VLOOKUP(A2,C:M,7,FALSE))` but 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, still not able to fit the other criteria of filtering the count based on value in column B – Sarah Oct 03 '16 at 13:13
  • did you try `=COUNTIFS(data!G:N,A2,B:B,"abc")` with two criteria? That was my suggestion – David Andrei Ned Oct 03 '16 at 14:15
  • Yes I did, it returned me 0 although there are multiple occurances =COUNTIFS(C:C,A2,J:J,"abc") – Sarah Oct 03 '16 at 14:16
  • The formula will work only if there are cells in the J column with values of exactly "abc". For instance "1 abc" "1, abc" " abc" "abc " will not work (see my revised example above) – David Andrei Ned Oct 03 '16 at 14:52