countif with dates

laura.buchanan

Can anyone tell me what might be wrong with this formula?

I'm trying to count the number of items that are complete, as noted by the presence of a date in the date completed field. I need to know the number of fields that have a date in 2018. Thanks

=COUNTIF([Date Completed]1:[Date Completed]14, "=>1/1/18")

J. Craig Williams

=COUNTIFS([Date Completed]1:[Date Completed]14, IFERROR(YEAR(@cell), 0) = 2018)

1. This only covers rows 1-14. You can cover the whole column like this:

=COUNTIFS([Date Completed]:[Date Completed], IFERROR(YEAR(@cell), 0) = 2018)

2. I always use COUNTIFS instead COUNTIF. It does the same thing, but with more possibilities later.

3. The formula throws an error when a [Date Completed] cell is blank. To resolve, this, I wrap the YEAR(@cell) with IFERROR and return 0 for the blanks or text, which won' match 2018.

4. 2018 is a number, "2018" is a text and they are not the same. YEAR() returns a number.

5. You original formula used => which implies 2018 and later. You can change the formula to do that (>= not => - remember it is 'greater than or equal to' not 'equal to or greater than')

I hope this helps.

Craig

laura.buchanan

Craig, I can't thank you enough. I was beating my head up against the wall with the countif formulas. Thanks SO much for responding to my question! I just LOVE Smartsheets! Laura

J. Craig Williams

You are welcome and welcome to the Community.

Craig

Paul Newcome

Another thing to keep in mind... To use a specific date in Smartsheet formulas, you need to use DATE(mm,dd,yyyy). Smartsheet will read 1/1/2018 as one divided by one divided by two thousand and eighteen.

 

Also, when using quotes in a formula's criteria, Smartsheet will look for that specific text.So using the example above, if you input 1/1/2018 it will look for the result above, but "1/1/2018" means it is looking for that specific text in a cell (which is still different from the DATE function.

 

Just a few pointers from someone who has learned things the hard way.

J. Craig Williams

DATE(YYYY,MM,DD)

(as numbers)

Paul Newcome

Good catch. Thanks.

J. Craig Williams

You are welcome. Always nice to have a second pair of eyes on the problem.

Craig

Neil_Fisher

Mr. Williams,

I have a status report SS that links back to another sheet where we've link to another sheet to count the number of scheduled and completed projects (chapters in a book and other steps).  I'd like a column that enters the number of projects that were previously completed.  We use this SS to give a weekly report to our managers.  I think the COUNTIFS function is what we need, but am stuck with how to count what was previously completed.

I hope this is enough information and I am clear what I am looking for.

Thanks for your help.

P.S., The columns left to right are:  11 is number of chapters, followed by total scheduled, completed, previous completed (which we currently just are imputing a number but looking for a formula), change, variance and progress.

 

Jose Munoz

Laura, 

 Can you please be kind and sahre on the post the formula that worked based on the advise given? Im trying to do something similar 

Thanks

Paul Newcome

The formula Craig provided should have been it based on what I read in the rest of the thread.

 

=COUNTIFS([Date Completed]:[Date Completed], IFERROR(YEAR(@cell), 0) = 2018)

.

If you are looking for something more specific to your use, feel free to give more details, and we may be able to help you with a solution that better fits your needs.

Jose Munoz

We are trying to  reference a Master Sheet and count how many projects were handled in 2018 and how many in 2019 so far 

our Date columns are Start Date and End Date

Paul Newcome

Then you would just have to determine which date column you wanted to base it off of. You would also want to think about how you would want to count projects that over lap years. If it started in 2018 and ended in 2019, would you want to count it for both years, just 2018, or just 2019?

Jose Munoz

We will use Start Date column and for those projects that started in 2018 and ended in 2019 will be count as 2018. 

will that help?

Paul Newcome

Very straightforward then...

 

Give this a try:

 

=COUNTIFS([Start Date]:[Start Date], IFERROR(YEAR(@cell), 0) = 2018)

.

Of course you would change 2018 to 2019 to count for 2019 and you will want to use the correct column names for your start date column.

Jose Munoz

We are getting a #unparseable error, Im doing the calculation on a separate sheet making a refence to the master sheet and the name is in bold. 

 

=COUNTIFS({StartDate_Range}, ([Start Date]:[Start Date], IFERROR(YEAR(@cell), 0) = 2018)