# Count Ifs using multiple criteria sorted by date

1. ## 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.

2. ## 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"

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

#### 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