Countif Range not working

  • Thread starter Thread starter tommcbrny
  • Start date Start date
T

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
 
tommcbrny said:
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
Click to expand...


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)
 
Oh wow, that is AWFUL proof reading on my part, sorry for the dumb post and
thanks Glenn for pointing out the error.
Tom
 
Thanks for the consolidation Bob, very useful!

Bob Phillips said:
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)
Click to expand...
 
Back
Top