COUNTIFS Function to count dates within a single column

Hi,

Is anyone able to identify the issue with the formula I'm using.

=COUNTIFS([2022/07/01]:[2022/12/31], IFERROR(YEAR(@cell), 0) = 2022)

Its purpose count a column that has dates within a certain date range for a summary table. I've done YYYY-MM-DD format and MM/DD/YYYY but I keep getting the same error "#UNPARSEABLE". Thank you!

Answers

  • Dan W
    Dan W ✭✭✭✭✭
    edited 07/07/22

    Are you only counting one column?

    You have [2022/07/01]:[2022/12/31]

    If you only want to count your Date added column then

    =COUNTIFS([Date added]:[Date added], IFERROR(YEAR(@cell), 0) = 2022)

    This will count only dates that have the year 2022

  • @Dan W

    Yeah, my intention was to display a count for all of the dates within a certain date range.

    For example: I have 20 submissions with dates ranging for the 2nd half of the year. Since each row has a date of when that submission was made, I want a formula that counts all of those dates instead of me counting how many submissions were inputted into the sheet between 06/01/2022 and 12/31/2022.

    I think that's the mistake I keep running into is counting the entire column rather then specifying a date range.

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭

    @Ammar Zafar why would you not move it to a report and that would do the grouping for you as well as the counting?

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

  • Dan W
    Dan W ✭✭✭✭✭
    edited 07/07/22

    If you would like to have a count for each date range then you would use

    =COUNTIFS([date added]:[date added], >=DATE(2022, 1, 1), [date added]:[date added], <=DATE(2022, 1, 31))

    You would need to use this formula separately for each range. You could use this formula in a sheet summery for each range as well .

    I used the date range 1/1/2022-1/31/2022 as an example


    Here is a link for usage of sheet summary

    https://help.smartsheet.com/learning-track/level-2-intermediate-users/sheet-summary

  • @Brent Wilson

    I have multiple sheets where I'm trying to group certain pieces of information from the sheets that I can transfer to a Summary Sheet.

    I don't want to make multiple reports for each sheet and then use those individual reports to push information to the Summary Sheet.

    Hope that clears my intent since I find this solution to be significantly more efficient.

  • @Dan W

    That works perfectly for my use. Thank you!! Much Appreciated!!

  • Dan W
    Dan W ✭✭✭✭✭

    @Ammar Zafar Excellent! Glad we could get it figured out.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!