Countif Range not working

[tommcbrny]

Hi,

I have the following entered to set a text value in column d when some other
text value is found in a cell range within columns b and c:

=IF(COUNTIF(B49:C49,"*aaa*")>0,"mgr1",IF(COUNTIF(B49,"*bbb*")>0,"mgr1",IF(COUNTIF(B49,"*ccc*")>0,"mgr1",IF(COUNTIF(B49,"*ddd*")>0,"mgr1",IF(COUNTIF(B49,"*eee*")>0,"mgr2",IF(COUNTIF(B49,"*fff*")>0,"mgr2","Other"))))))

This works as intended for any value containined in cells of column b, but
only works for the very first value ("aaa") in column c. In other words, if
the value of a cell within range for column c is "bbb", "Other" is returned
instead of "mgr1".

Can anyone tell me what I am doing wrong? Is there a way to have the
formula evaluate each value for both columns?

Thanks,

Tom

 

[Glenn]

Hi,

I have the following entered to set a text value in column d when some other
text value is found in a cell range within columns b and c:

=IF(COUNTIF(B49:C49,"*aaa*")>0,"mgr1",IF(COUNTIF(B49,"*bbb*")>0,"mgr1",IF(COUNTIF(B49,"*ccc*")>0,"mgr1",IF(COUNTIF(B49,"*ddd*")>0,"mgr1",IF(COUNTIF(B49,"*eee*")>0,"mgr2",IF(COUNTIF(B49,"*fff*")>0,"mgr2","Other"))))))

This works as intended for any value containined in cells of column b, but
only works for the very first value ("aaa") in column c. In other words, if
the value of a cell within range for column c is "bbb", "Other" is returned
instead of "mgr1".

Can anyone tell me what I am doing wrong? Is there a way to have the
formula evaluate each value for both columns?

Thanks,

Tom

You aren't referencing column C for "bbb".

 

[Bob Phillips]

Works fine here Tom, which suggests a data problem to me.

BTW, you can reduce the formula to

=IF(SUM(COUNTIF(B49:C49,{"*aaa*","*bbb*","*ccc*","*ddd*"})),"mgr1",IF(SUM(COUNTIF(B49,{"*eee*","*fff*"})),"mgr2","Other")))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[tommcbrny]

Oh wow, that is AWFUL proof reading on my part, sorry for the dumb post and
thanks Glenn for pointing out the error.
Tom

 

[tommcbrny]

Thanks for the consolidation Bob, very useful!

Works fine here Tom, which suggests a data problem to me.

BTW, you can reduce the formula to

=IF(SUM(COUNTIF(B49:C49,{"*aaa*","*bbb*","*ccc*","*ddd*"})),"mgr1",IF(SUM(COUNTIF(B49,{"*eee*","*fff*"})),"mgr2","Other")))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)