+ Reply to Thread
Results 1 to 4 of 4

COUNTIFS to count blank cells as a criteria

  1. #1
    Forum Contributor
    Join Date
    11-06-2007
    Posts
    123

    COUNTIFS to count blank cells as a criteria

    Hi

    I am trying to count the "current" employees in each month, including those who have started and left within the month i.e. excluding those who have left and who have not yet started
    I have tried using COUNTIFS to count ""s (blanks) as a criteria but keep getting 0. I have a long winded formula but there is I am sure a much better way.

    Please see attached.

    Thanks

    Cortlyn
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,398

    Re: COUNTIFS to count blank cells as a criteria

    Try this:

    =COUNTIFS(Leaving_Date,">="&AQ$1,Leaving_Date,"<="&EOMONTH(AQ$1,0))+COUNTIF(Leaving_Date,"")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor
    Join Date
    11-06-2007
    Posts
    123

    Re: COUNTIFS to count blank cells as a criteria

    Thanks AliGM. I was trying to put it all into one formula but this way works so I will live with it,

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,398

    Re: COUNTIFS to count blank cells as a criteria

    You can't make it part of the COUNTIFS formula with the other criteria, as it would be an AND criterion, not OR. Glad to gave helped!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 12
    Last Post: 03-23-2017, 01:24 PM
  2. [SOLVED] Need to count all non blank cells in column that meet criteria of two seperaate values
    By tomcoll67 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-26-2014, 04:15 PM
  3. COUNTIFS, multiple criteria and not including blank cells
    By qhoney in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-07-2014, 05:14 PM
  4. [SOLVED] CountIFS with multiple criteria to ignor blank cells
    By dbaker4020 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-02-2013, 09:03 AM
  5. Replies: 2
    Last Post: 08-18-2011, 11:33 AM
  6. [SOLVED] count non blank cells with criteria
    By UT in forum Excel General
    Replies: 5
    Last Post: 04-25-2006, 02:40 PM
  7. count non blank cells which meet criteria in another column
    By cmarsh5035 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-16-2005, 12:45 PM

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