+ Reply to Thread
Results 1 to 2 of 2

Count Ifs using multiple criteria sorted by date

  1. #1
    Registered User
    Join Date
    08-03-2017
    Location
    Australia, Brisbane QLD
    MS-Off Ver
    2016
    Posts
    8

    Count Ifs using multiple criteria sorted by date

    Hi I am trying to create stats table to show the following:
    - Number of Audits conducted prior to 2017 and how many have been closed.
    - Number of Audits conducted in 2017 and how many have been closed.

    I can use =COUNTIF(B11:B29,"<="&B1) to calculate the total number of audits conducted prior to 2017 but i cant seem to separate the data by type of Audit conducted and number closed.
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Count Ifs using multiple criteria sorted by date

    hi there. i don't think =COUNTIF(B11:B29," < ="&B1) gives you the correct count. you see, dates are actually recognized as numbers. if you format cell B11 as General, it shows you 42208. that is 42208th day from 1-Jan-1900. so as recent years are going to be in the 40,000+ category, your criteria of < =2017 is going to be none of the dates.

    you can use this in B5:
    =COUNTIFS($A$11:$A$29,A5,$B$11:$B$29," < 1jan2017")

    and in C5:
    =COUNTIFS($C$11:$C$29,"Closed",$B$11:$B$29," < 1jan2017")

    also, consider to input the desired results so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand.

    ps: there seems to be some problem if i connect the less than sign with anything, hence i put a space in it. please remove all spaces like this:
    " < 1jan2017"

+ 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: 4
    Last Post: 04-28-2017, 11:06 AM
  2. Replies: 1
    Last Post: 09-04-2014, 12:14 PM
  3. Date Count By Month with Multiple Criteria
    By usc1382 in forum Excel General
    Replies: 7
    Last Post: 09-27-2011, 09:39 AM
  4. Count - multiple criteria, multiple sheets, and date range.
    By threecliffs in forum Excel General
    Replies: 6
    Last Post: 06-14-2011, 01:36 PM
  5. Replies: 4
    Last Post: 04-08-2011, 05:24 PM
  6. Count Multiple Criteria within Multiple Date Ranges
    By E6BAV8R in forum Excel General
    Replies: 3
    Last Post: 12-08-2010, 05:06 PM
  7. count based on multiple date criteria
    By lisaw in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 09-06-2005, 07:05 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