COUNTIF not including something in both columns

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
 
S

Shane Devenshire

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top