+ Reply to Thread
Results 1 to 4 of 4

Counting data in non blank cells and exclude certain data from the count

  1. #1
    Registered User
    Join Date
    06-12-2007
    Posts
    11

    Counting data in non blank cells and exclude certain data from the count

    Hopefully I can make myself clear with this one!

    I am currently using the COUNTA formula to count non blank cells for a specific range but need to if possible to exclude from the count cells that contain specific data as well a blank data.

    I'll try and explain!

    The data I wish to exlude in the count are cells that are blank AND contain either Block, Not AM or Not PM

    Eg:
    Cell A1 = John
    Cell A2 = Block
    Cell A3 = Not AM
    Cell A4 = Graham
    Cell A5 is blank (no data)
    Cell A6 = Sue
    Cell A7 = Not PM

    I need a formaular that will look at cells A1:A7 and will return the figure 3 as in my example (i.e. A1 as it contains John, A4 as it contains Graham and Cell A6 as it contains Sue as the other 4 are either blank or contain Block, Not AM or Not PM.

    Hope this is clear and any help will be very much appriciated.

    Many thanks
    John

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885
    John, try this formula:
    Please Login or Register  to view this content.

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Another way...

    =SUMPRODUCT(--(A1:A7<>""),--ISNA(MATCH(A1:A7,{"Block","Not AM","Not PM"},0)))

  4. #4
    Registered User
    Join Date
    06-12-2007
    Posts
    11
    Thank you very much to both of you.

    It's easy when you know how!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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