countif with dates

=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

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.

DATE(YYYY,MM,DD)

(as numbers)

Good catch. Thanks.

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.

Smart sheet.png

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

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.

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

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?

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?

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.