Using countifs across sheets to hold metrics for reports

bbemisbbemis ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
03/28/18 Edited 12/09/19

We have a sheet that lists all of our projects (there are over 300 at the moment). We used to keep all of our metrics at the top of the sheet, but every now and again, someone would sort by a value that caused the metrics to get mixed into the project list.  So when cross-sheet formulas (vlookup, countifs, etc) became available, I created a new sheet that totaled all of the various metrics regarding our projects separately, rather than keep them in the same sheet.  So, I have the original project sheet and the metrics sheet.  The metrics sheet is essentially a large set of countifs.

Now, I'm building out dashboards and reports. I show the metrics on the dashboard, and if someone wants more info, they can click on it and go to the report.  

My question is actually about the metrics sheet...if I don't go in and save it regularly, will the metrics get updated on my dashboards?  Or will they only show the last "saved" number?


  • Mike WildayMike Wilday ✭✭✭✭✭

    Good question. Based on the fact that its a formula... you would probably have to access the sheet regularly to update the numbers. Could an employee? OR other experienced user confirm? 

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    You Metrics/KPI will be updated without someone viewing it. I have seen no issues with this except a slight delay sometimes.

    The only "if a trees a falls in the forest" issue I know of when the formula reference the TODAY() function in any way. Then you will need to someone refresh that sheet. That is true whether the formula is on the Metrics/KPI sheet or the sheets feeding it.


  • bbemisbbemis ✭✭✭✭✭

    Thanks, Craig.  Yes, I am using the TODAY() function...that would explain the strangeness I was seeing.  Guess I'll have to plan to save that Metrics sheet on a daily basis  :)  

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    If you already use Zapier, try this:

    If not, for small numbers of sheets, it is the way I go. 


Sign In or Register to comment.