COUNTIFS between dates & criteria.
Answers
-
@Paul Newcome hah, that was my mistake. I took it out while messing with the formula earlier and missed putting it back in. It works now, thanks for the help!
-
@Paul Newcome I've been reading along this thread and I've been trying to do the same as most people. In a single sheet, I am trying to count the number of rows with the following criteria:
- Project Status = Complete
- Age (Completed) >0
- End Date >=2022,01,01
- End Date<=2022,12,31
In a Report, I can validate the count but when I create a Summary field in my Sheet, with the following formula:
=COUNTIFS([Project Status]:[Project Status], "Complete", [Age (Completed)]:[Age (Completed)], >0, [End Date]:[End Date], >= DATE(2022, 1, 1), [End Date]:[End Date], <= DATE(2022, 12, 31)))
My result is: #UNPARSEABLE
Not sure what I am missing. My end result is to create this per year to populate a Widget on a Dashboard.
Many thanks in advance!
-
@Nick Stamatakis Try removing a closing parenthesis from the very end. It looks like you have one too many.
-
Thanks @Paul Newcome Now my issue is that I get an #Invalid Operation... thoughts?
-
@Nick Stamatakis Is that error present in any of the cells being referenced?
It also looks like you are just trying to pull for the year 2022. If that is the case we can simplify a bit and account for blanks/non-dates in the date column like so:
=COUNTIFS([Project Status]:[Project Status], "Complete", [Age (Completed)]:[Age (Completed)], >0, [End Date]:[End Date], IFERROR(YEAR(@cell), 0) = 2022)
-
@Paul Newcome Still an #INVALID OPERATION
My end goal is to have a widget, per year, to show how many projects we've completed per year. Open to suggestions on how to do this. Eventually it would be cool to have this in a bar graph but baby steps :-)
-
@Nick Stamatakis Have you double checked that the date column is in fact set as a date type column?
-
-
@Nick Stamatakis Ok. Is that error present in even a single cell within any of the referenced columns? If not... How exactly are each of the referenced columns being populated?
It shouldn't make a difference, but lets give it a shot with @cell references:
=COUNTIFS([Project Status]:[Project Status], @cell = "Complete", [Age (Completed)]:[Age (Completed)], @cell >0, [End Date]:[End Date], IFERROR(YEAR(@cell), 0) = 2022)
-
@Paul Newcome I have a similar formula that references two columns "Program" and "Date". What is throwing the error in this statement:
=COUNTIFS({Program}, District@row, {Date}, AND(>/= DATE(2022,07,01), </= DATE(2021,06,30))
-
@Summer Edwards It looks like you have some syntax issues with your operators.
=COUNTIFS({Program}, District@row, {Date}, AND(@cell>= DATE(2022,07,01), @cell<= DATE(2021,06,30)))
-
Ah! Thank you @Paul Newcome 😁
-
-
Need Help. I use COUNTIF to define a Date Range & Status (see below):
=COUNTIFS("{Start Date}", ">=Date(2023/3/13)", "{Finish Date}", "<=Date(2023/5/1)", "{Status}", "Green")
Suppose returns Green tasks number but it returns 0.
Any suggestion?
Thanks,
-
Hey @David Chen
The formula does not have the syntax that smartsheet is expecting. Only textstrings should be enclosed in parentheses.
Try this
=COUNTIFS({Start Date}, >=Date(2023/3/13), {Finish Date}, <=Date(2023/5/1), {Status}, "Green")
Does this work for you?
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!