COUNTIFS between dates & criteria.

Ryon · 07/20/22

@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!

Nick Stamatakis · 11/16/22

@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!

Paul Newcome · 11/16/22

@Nick Stamatakis Try removing a closing parenthesis from the very end. It looks like you have one too many.

Nick Stamatakis · 11/16/22

Thanks @Paul Newcome Now my issue is that I get an #Invalid Operation... thoughts?

Paul Newcome · 11/16/22

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

Nick Stamatakis · 11/16/22

@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 :-)

Paul Newcome · 11/16/22

@Nick Stamatakis Have you double checked that the date column is in fact set as a date type column?

Nick Stamatakis · 11/16/22

@Paul Newcome Yes sir:

Screen Shot 2022-11-16 at 11.31.17 AM.png

Screen Shot 2022-11-16 at 11.31.44 AM.png

Screen Shot 2022-11-16 at 11.31.50 AM.png

Paul Newcome · 11/17/22

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

Summer Edwards · 11/30/22

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

Paul Newcome · 12/01/22

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

Summer Edwards · 12/01/22

Ah! Thank you @Paul Newcome 😁

Paul Newcome · 12/01/22

@Summer Edwards Happy to help. 👍️

David Chen · 03/18/23

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,

Kelly Moore · 03/18/23

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

COUNTIFS between dates & criteria.

Answers

Help Article Resources

Categories