Trying to apply a date range filter on a countif, but getting an unparseable error
I can get the count with
=COUNTIFS({2020-03 Monthly Complaints Range 1}, =[Manufacturing location]1)
but when I add the filter >= to a date referenced in a cell. I get the unparseable error.
=COUNTIFS({2020-03 Monthly Complaints Range 1}, =[Manufacturing location]1), {2020-03 Monthly Complaints rec date 1}, >=[From Date]1)
Can someone tell me if my syntax is wrong and you can correct it?
Answers
-
You only need to remove the closing parenthesis from between your first criteria and second range.
-
Changed to:
=COUNTIFS({2020-03 Monthly Complaints Range 1}, =[Manufacturing location]1, {2020-03 Monthly Complaints rec date 1}, >=[From Date]1)
Now get a #invalid Ref error message.
-
How are you creating your cross sheet references?
-
I created a 'roll up' sheet. (data comes from 2 SmartSheets, feeds to this roll up report, that feeds my dashboard) on this sheet I have the formulas. Later I take the values from the roll up sheet and use them for my dashboard. It works quite well. I am just having some trouble filtering the data from the sheet. When I do the countif, all occurrences are counted, I want to only count if they are within a date range.
Thanks
-
Are the two columns you are using for dates actually formatted as date type columns in the column properties?
-
Source (ss) = 03/27/2020 12:16:39
Roll up (pick from a calendar icon) = 03/02/20
-
Is the source a system generated column or manually entered?
-
it is generated by an outside program (nothing to do with SS at all.) I cut and paste the date from the outside program into my ss.
-
There is the issue. When you paste it into SS, you are pasting a text value. You will need to use a "helper column" to pull the date portion and format it as a date so that it can be used.
In the "helper" column you wan tot use something along the lines of...
=DATE(VALUE(MID([Paste Column Name]@row, 7, 4)), VALUE(LEFT([Paste Column Name]@row, 2)), VALUE(MID([Paste Column Name]@row, 4, 2)))
Make sure the "helper" column is formatted as a date type column in the column properties, and then use this one for your cross sheet reference.
-
Can i simply change the column format from Text/number to 'date'? I don't want to change the structure of the table if possible.
-
It will still be imported as text and need to be converted.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!