Countif Range not working

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(B 49,"*bbb*")0,"mgr1",IF(COUNTIF(B49,"*ccc*")0,"mg r1",IF(COUNTIF(B49,"*ddd*")0,"mgr1",IF(COUNTIF(B4 9,"*eee*")0,"mgr2",IF(COUNTIF(B49,"*fff*")0,"mgr 2","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

tommcbrny wrote:
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(B 49,"*bbb*")0,"mgr1",IF(COUNTIF(B49,"*ccc*")0,"mg r1",IF(COUNTIF(B49,"*ddd*")0,"mgr1",IF(COUNTIF(B4 9,"*eee*")0,"mgr2",IF(COUNTIF(B49,"*fff*")0,"mgr 2","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".

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" wrote in message
...
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(B 49,"*bbb*")0,"mgr1",IF(COUNTIF(B49,"*ccc*")0,"mg r1",IF(COUNTIF(B49,"*ddd*")0,"mgr1",IF(COUNTIF(B4 9,"*eee*")0,"mgr2",IF(COUNTIF(B49,"*fff*")0,"mgr 2","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

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

"Glenn" wrote:

tommcbrny wrote:
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(B 49,"*bbb*")0,"mgr1",IF(COUNTIF(B49,"*ccc*")0,"mg r1",IF(COUNTIF(B49,"*ddd*")0,"mgr1",IF(COUNTIF(B4 9,"*eee*")0,"mgr2",IF(COUNTIF(B49,"*fff*")0,"mgr 2","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".

Thanks for the consolidation Bob, very useful!

"Bob Phillips" wrote:

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" wrote in message
...
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(B 49,"*bbb*")0,"mgr1",IF(COUNTIF(B49,"*ccc*")0,"mg r1",IF(COUNTIF(B49,"*ddd*")0,"mgr1",IF(COUNTIF(B4 9,"*eee*")0,"mgr2",IF(COUNTIF(B49,"*fff*")0,"mgr 2","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