+ Reply to Thread
Results 1 to 4 of 4

Formula that will count names but reject the word BLOCKED

  1. #1
    Registered User
    Join Date
    12-21-2012
    Location
    Springfield, MA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Formula that will count names but reject the word BLOCKED

    I work on a medical unit where I am building a unit tracker for patients. Currently I am using the formula:

    =IF(ISNONTEXT('Daily Census'!C3)=TRUE,0,1)

    to count the names and add them up in cell A2. The issue I have is sometimes we have to block a bed or room and use the work BLOCKED to identify it. However when I type it in, it counts it for the total count. I need to change the formula so it would count names but ignore the specified term "BLOCKED". Thanks for anyone who can help. Example below of how it looks set up.

    Census PATIENT
    5 Room
    1 516-1 BLOCKED
    1 516-2 Patient A
    1 515-1 Patient B
    1 515-2 Patient C
    1 514 Patient D

    So for 516-1 It says Blocked and is counting it with a 1. I would need it to exclude the word BLOCKED and say a 0. Again thanks for any help.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Formula that will count names but reject the word BLOCKED

    Hi

    It appears that you are using a formula on every row to evaluate entries in column C. However given every entry (based on your example) is by definition a text entry, I don't see why you want to use ISNONTEXT()

    You could use instead
    =IF(ISERROR(SEARCH("Blocked",A3)),1,0)
    copy down every row and then add up all the 1s

    Alternatively you could use the following formula in a single cell
    =COUNTA(A:A)-COUNTIF(A:A,"*blocked")-2

    The -2 is to deduct the first two rows containing Census PATIENT & 5 Room
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Formula that will count names but reject the word BLOCKED

    Try..

    Please Login or Register  to view this content.
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Registered User
    Join Date
    12-21-2012
    Location
    Springfield, MA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Formula that will count names but reject the word BLOCKED

    Thank you Ace, that solved the problem for me. Exactly what I needed.
    Last edited by armynurse; 12-21-2012 at 06:25 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1