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
-
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
-
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.
-
@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
-
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
-
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.
-
That works perfectly for my use. Thank you!! Much Appreciated!!
-
@Ammar Zafar Excellent! Glad we could get it figured out.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!