Trying to apply a date range filter on a countif, but getting an unparseable error

Stephen Paolicelli

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?

Paul Newcome

You only need to remove the closing parenthesis from between your first criteria and second range.

Stephen Paolicelli

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.

Paul Newcome

How are you creating your cross sheet references?

Stephen Paolicelli

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

Paul Newcome

Are the two columns you are using for dates actually formatted as date type columns in the column properties?

Stephen Paolicelli

Source (ss) = 03/27/2020 12:16:39

Roll up (pick from a calendar icon) = 03/02/20

Paul Newcome

Is the source a system generated column or manually entered?

Stephen Paolicelli

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.

Paul Newcome

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.

Stephen Paolicelli

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.

Paul Newcome

It will still be imported as text and need to be converted.