Widget filter, IF statement, or what?

M2

I have a dashboard that provides counts of certain data. One of the counts needs to be date-driven in the respect that it needs to show the count for the current year.  How do I build that into the widget?

Mike Wilday

You'll need to start by creating that metric somewhere. In a new sheet you can use a cross-sheet formula to count all of the projects that are in your project sheet. I imagine you can do that by looking at the due-date. 

1. Type =countif(
2. click on create a cross-sheet reference in the help reference that drops down.
3. Select your main project sheet in the left menu, and then select the due date column and press okay. 
4. then add a comma, and the following text: IFERROR(Year(@cell),0) = 2019

The IFerror will count cells without dates or that are blank as 0. 

The final formula should look something like this... 

=COUNTIFS({Name of Cross Sheet refrerence}, IFERROR(YEAR(@cell), 0) = 2019)

Mike Wilday

Then in your metric widget. You will point to the sheet that you just created and include that metric. I like to use one cross sheet reference for most of my dashboard calculations. But occasionally, I have to use more than one if we are performing metrics on multiple sheets.