20

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

In reply to by laura.buchanan

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

In reply to by Josem29

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.

In reply to by Paul Newcome

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

In reply to by Josem29

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?

In reply to by Josem29

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.

In reply to by Paul Newcome

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)

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.

 

Hi All,

Attempting to count dates to see how many of a specific item has been completed on a specific date.

I am using the below formula =COUNTIFS({Column Rectification Tracking Range 5}, "2018.08.28") but am getting O count

Any idea what I am doing incorrect?

In reply to by A.Luc

When referencing a specific date within a formula, you need to use the date function.

 

=COUNTIFS({Column Rectification Tracking Range 5}, @cell = DATE(2018, 085, 28))