+ 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,147

    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"

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

+ 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