Filtering/Illustrating Data From Date Columns on Dashboard

megtro610

I am making a dashboard, and I would like to make the specific year's number change as I add/ delete sections in my sheet. (right now each number is manually typed from a report I made).

I have each item filtered by year, and then each item has its own specific date on the sheet.

What formula can I use to do this on a metric sheet?

markkrebs

Id structure the sheet a bit differently but with the existing structure you'd need to do something like this:

create a column to count the ancestors (I always add another to count children too) and a helper column with a formula:

=If(ancestors@row=1,year@row,parent()). then make it a column formula

then your metric formula can be something like:

=Countifs({year ref}, year@row,{children ref},0)

megtro610

@markkrebs I'm sorry I'm new at Smartsheet. I made a mistake in my post. I'm just trying to count the parent (blue rows). What do I write in my helper row on my main sheet and then in my metric sheet to count how many 2021s there are for example?

markkrebs

@megtro610 might be easier to hop on a call to work through. if you want send an invite to mkrebs@alixpartners.com