Countifs Past 90ds referencing another sheet

JRinger

Hi,

I am trying to make a dashboard that shows the top 10 pieces of equipment with the highest number of issues in the past 90d.

I have my primary sheet that collects the data (Primary), and a reference sheet I am using to collect a count. I am just struggling to have it filter the last 90d. Showing #invalidoperation

=COUNTIFS({Emergency Work Order_Anniston Range 1}, CONTAINS(Equipment@row, @cell), AND({Emergency Work Order_Anniston Range 4} >= TODAY(-90)))

I tried to initially use the report to filter it down, but it shows all equipment issues, and unable to show only the top 10 on the dashboard.

Thanks in advance for any help!

Mark.poole

The problem with the Today() formula is that it only updates while the sheet is open. There are some workarounds for that.

also how large is the range for {Emergency Work Order_Anniston Range 1}

JRinger

Hi Mark,

The range is 5590 lines.

Thanks in advance for your help!

Mark.poole

Is it more then one column?

Kelly Moore

Hey @JRinger

In addition to the Today function mentioned by Mark, the COUNTIFS may a syntax error. Please try this

=COUNTIFS({Emergency Work Order_Anniston Range 1}, CONTAINS(Equipment@row, @cell), {Emergency Work Order_Anniston Range 4}, >= TODAY(-90))

Also, for assistance in automatically updating the TODAY function, please see this link from Andrée Starå:

https://community.smartsheet.com/discussion/74527/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar

Kelly