# Pulling data only for current year

Options
✭✭✭✭

I have a metric sheet pulling in data from a pipeline report to show how many closed deals we have for that region from 2016 thru to today. I now need to add a formula to show only the closed deals within that region for 2020 and display that in a new column.

The formula I used to pull the entire closed deals was:

Any help would be appreciated. Thanks!

• ✭✭✭✭✭✭
Options

Hi @Deb White,

Try:

=COUNTIFS({Business Unit}, [Business Unit]1, {Stage}, Stage\$9, {insert close date range}, YEAR(@cell)=2020).

This just adds a 3rd criteria to your function that looks at the close date range and the year being 2020. All 3 criteria have to be met in order for it to be counted.

Work?

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭
Options

Hi Mark,

Thanks for your response. Unfortunately, it did not. BUT, I'm sure it's user error. I am very new to formulas.

I amended the formula to:

=COUNTIFS({Business Unit}, [Business Unit]1, {Stage}, Stage9, {Target / Actual Close}, YEAR (@cell)=2020)

I get an unparseable error. The Target/Actual Close is the column from my pipeline that will show that date the deal was closed. It will have a data such as 08/28/2020.

Regards,

Deb

• ✭✭✭✭✭✭
Options

Hi @Deb White ,

OK, plan 2. Can you insert a helper column into your sheet where you can calculate the close year? The column formula would be =IFERROR(YEAR([Target / Actual Close]@row),""). Then change your COUNTIFS statement to =COUNTIFS({Business Unit}, [Business Unit]1, {Stage}, Stage9, {Close}, =2020) where {Close} is the helper column with your calculated close year.

There is almost always a way. Sometimes it takes a few tries.

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭
edited 12/03/20
Options

Is your date field formatted as a date column? Try using an IF statement before the countifs.

I would re-select the two fields where your criteria is ([Business Unit]1 and Stage\$9) just to confirm those are mapped correctly.

=IF(AND(YEAR(TODAY()) = YEAR({Target / Actual Close})), COUNTIFS({Business Unit}, [Business Unit]1, {Stage}, Stage\$9),"")

• ✭✭✭✭
Options

Mark - when I added the helper row as you suggest I don't get an error message, however, nothing populates in that column. When I then adjusted my COUNTIFS formula I get an incorrect argument

Jenna, I know my criteria for business unit and stage are correct as I have a count from 2016 onwards. I tried your formula but I get an invalid data type error. I don't understand what the and year today stuff is asking.

I do appreciate both of you jumping in on this.

• ✭✭✭✭
Options

Jenna/Mark - thank you both! The Helper column helped and Jenna appreciated you explaining what the formula is actually doing so I can learn!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!