Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jun 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count Unique Cells Containing Text but exclude "No DT" from count

    I'm using =SUM(IF(LEN(D11:D14),1/COUNTIF(D11:D14,D11:D14)))as an array formula to only count up unique occurences of items in a list which works fine. I now however need to remove occurences of "No DT" from this number....

    Example:

    No DT
    No DT
    No DT
    AL

    Result is currently 2 but should be 1 as I don't want to count "No DT"

    Any ideas?

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Mattock12

    ..welcome to the Lounge!

    You just need to adjust your formula to subtract 1 if there are any "No DT" in the range, so use this:
    Code:
    =SUM(IF(LEN(D11:D14),1/COUNTIF(D11:D14,D11:D14)))-(COUNTIF(D11:D14,"No DT")>0)
    zeddy
    (in-hospital-beddy)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •