countif with dates

laura.buchananlaura.buchanan ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
09/16/18 Edited 12/09/19

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

Previous1

Comments

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    =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.buchananlaura.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 WilliamsJ. Craig Williams Top Contributor

    You are welcome and welcome to the Community.

    Craig

  • Paul NewcomePaul 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.

    thinkspi.com

  • J. Craig WilliamsJ. Craig Williams Top Contributor
    edited 09/18/18

    DATE(YYYY,MM,DD)

    (as numbers)

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Good catch. Thanks.

    thinkspi.com

  • J. Craig WilliamsJ. Craig Williams Top Contributor

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

    Craig

  • edited 05/01/19

    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

  • Paul NewcomePaul 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.

    thinkspi.com

  • 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 NewcomePaul 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?

    thinkspi.com

  • 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 NewcomePaul 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.

    thinkspi.com

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

Sign In or Register to comment.