8

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")

Functionality
Industry

Comments

=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

You are welcome and welcome to the Community.

Craig

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.

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

Craig

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.