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

=SUMPRODUCT((A6:A10<"adam")*(B6:B10<"adam"))


Greetings from New Zealand

"Studebaker" wrote in message
...
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)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Studebaker" wrote:

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