Trying to create formula to exclude any cells that contain the word "D/C"

  • Hello everyone!


    I currently have a template that contains employee names and what I am looking to do is exclude any cell that would contain the word D/C infront of an employee's name, ex: D/C John Smith...I've tried many different functions and I haven't been able to figure it out, I've even tried to remove the slash, just in case it was affecting the formula, but still nothing. I've even tried to do the COUNTIF function, but I haven't yet been able to figure anything out.


    I am currently using the COUNTIF function and the COUNTBLANK function in order to exclude all of the cells that contain the words NOT REQUESTED and exclude the cells that are blank, and this is currently working great! My formula looks like this:



    =COUNTIF('MONDAY-TUESDAY'!C:C,"<>*NOT REQUESTED*")-COUNTBLANK('MONDAY-TUESDAY'!C:C)


    I've tried replacing the NOT REQUESTED with the word D/C, but it's not working...



    Any input would be greatly appreciated.


    Thank you in advance :)

  • Re: Trying to create formula to exclude any cells that contain the word &quot;D/C&quot;


    Hi Skywriter, thank you for your quick response, however, I've already tried that and it doesn't work. It gives me a random number

  • Re: Trying to create formula to exclude any cells that contain the word &quot;D/C&quot;


    You should share a sample workbook that this doesn't work in. I tested the D/C part to see if it works and it was fine, so we need to see your data.


    When you click reply there will be a new button labeled go advanced, click on that button then there will be a paperclip icon in the toolbar, click on that icon and follow the instructions.

  • Re: Trying to create formula to exclude any cells that contain the word &quot;D/C&quot;


    I think your problem is in using the whole C column.
    In my original sample test I tried it out on a 10 cell range.
    When I run a test with C:C, it doesn't work but if I limit it to say C1 to C1000 it works.
    Not sure why, but that's what is happening.

  • Re: Trying to create formula to exclude any cells that contain the word &quot;D/C&quot;


    Try this...
    [TABLE="class: grid"]

    [tr]


    [td]

    [/td]


    [td]


    L


    [/td]


    [td]


    M


    [/td]


    [td]


    N


    [/td]


    [/tr]


    [tr]


    [td]


    2


    [/td]


    [td]

    D/C aaa

    [/td]


    [td]

    [/td]


    [td]


    2


    [/td]


    [/tr]


    [tr]


    [td]


    3


    [/td]


    [td]

    bbb

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    4


    [/td]


    [td]

    D/C ccc

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    5


    [/td]


    [td]

    ddd

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    6


    [/td]


    [td]

    D/C eee

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [/TABLE]


    N2=COUNTIFS(L:L,"<>D/C*",L:L,"<>")


    @ skywriter, it is not working because all the empty cells are also <> "DC*"

  • Re: Trying to create formula to exclude any cells that contain the word &quot;D/C&quot;


    Thank you each and every one of you for your ideas!! The formula that has ended up working is the following:


    =COUNTIF('MONDAY-TUESDAY'!C:C,"<>*NOT REQUESTED*")-COUNTIF('MONDAY-TUESDAY'!C:C,"D/C*")-COUNTBLANK('MONDAY-TUESDAY'!C:C)


    I had to remove these <> for the formula to work properly!


    Thanks again everyone!