COUNTIF not including something in both columns

  • Thread starter Thread starter Studebaker
  • Start date Start date
S

Studebaker

Hello,

I have a list of names in two columns - column B lists the Assistants and
column C lists the Managers. I need to count the number of times the name
"adam" does not appear in column b and column c in the same row - in other
words where adam was neither an assistant or a manager.

I thought this formula would work but it doesn't:
=COUNTIF(B3:B9,"<>*adam*")+COUNTIF(C3:C9,"<>*adam*")

In this example below, the answer should be "3". Only 3 times does the name
"adam" not appear in column 1 and 2 in the same row - i.e., in the 2nd, 4th
and 5th row.

column 1 column 2
adam john
john jeff
greg adam
greg jeff
jeff edward

Please help with a formula.

Thank you
 
Hi,

In 2003:

=SUMPRODUCT(--(A1:A5<>F1),--(B1:B5<>F1))

where you enter adam in F1

In 2007:

=COUNTIFS(A1:A5,"<>adam",B1:B5,"<>adam")
or using the F1 idea:

=COUNTIFS(A1:A5,"<>"&F1,B1:B5,"<>"&F1)
 
Back
Top