How to make widget that counts year in date column?

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?

JCiabattoni

@megtro610

The formula will have to be edited slightly to pull the data from another sheet. Have you used the "Reference Another Sheet" link before? It's pretty straight forward.

1. Start to create your formula. When you get the part where you need to reference another sheet, click the link.
2. Select the column(s) you need for your formula. I like to fill in the "Sheet reference name" because it makes the formulas cleaner and reminds me what I am pulling.
3. Continue the formula as needed!

I hope this helps!
~Jaime

JCiabattoni

@megtro610,

Do you fill in the "Date Created" on each row? If so, in the "Year" column you can use this formula to pull out the year (make sure the "Date Created" is formatted to the Date type):

Then, in your Metric sheet, use the "CountIf" formula to count each year. Note: If you use a column to define the Criteria in this formula, you can update it YOY as needed.

Let me know if you have any questions!

~Jaime

megtro610

@Jaime Ciabattoni ,

I did not put a date created in every row. just in the parent row. I just want to count how many times it appears in the column.

JCiabattoni

That would require some form of "CountIf" with "Contains" formula. I tried to get it to work, but am having problems with it so far. Since you do have the "Year column" is there a reason you don't want to use it? If it's a matter of making the sheet look clean, you could always use conditional formatting to make the cells in those rows white text. Then you can't see it, but the easier formula will work!

JCiabattoni

@megtro610

I think I figured it out thanks to this post (https://community.smartsheet.com/discussion/75896/year-function?_gl=1ah0pxm_gcl_auNTc4NDM4MjI0LjE3MzM3NzM0Mzk._gaMTU3NjIwOTMzOS4xNzMzNzczNDM5_ga_ZYH7XNXMZK*MTczMzg0NjA5NS40LjEuMTczMzg0NjcyNC4xNC4wLjA).

Try this:

The IFERROR will help it not cause errors on the blank rows.

~Jaime

megtro610

I am referencing another sheet, and I keep getting a #UNPARSEABLE error. I've tried doing it in both the original and metric sheet.

JCiabattoni

@megtro610

The formula will have to be edited slightly to pull the data from another sheet. Have you used the "Reference Another Sheet" link before? It's pretty straight forward.

1. Start to create your formula. When you get the part where you need to reference another sheet, click the link.
2. Select the column(s) you need for your formula. I like to fill in the "Sheet reference name" because it makes the formulas cleaner and reminds me what I am pulling.
3. Continue the formula as needed!

I hope this helps!
~Jaime