Pulling data only for current year
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:
=COUNTIFS({Business Unit}, [Business Unit]1, {Stage}, Stage$9
Any help would be appreciated. Thanks!
Best Answer
-
@Deb White, Hi Deb,
I think referencing a helper column may be the easiest way to do this, as Mark suggested.
In your source sheet create a new column, within that column type in the formula
=IFERROR(IF(AND(YEAR(TODAY()) = YEAR([Target / Actual Close]@row)), 1, 0), 0)
What this does is takes a look at your Target / Actual Close column and verifies if the year matches what the year of today's date is then kicks out the number 1 which you can change to say "Current Year", or "Yes".. I just use the 1 in this place so that I can easily reference this in a cross-sheet formula. Once you have done that in Row 1 you can right-click and choose 'Convert to Column Formula' so that all the rows will be populated appropriately.
From here you will change your formula as follows: =COUNTIFS({Business Unit}, [Business Unit]1, {Stage}, Stage$9, {Target / Actual Close}, 1)
Answers
-
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.
-
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
-
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.
-
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),"")
-
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.
-
@Deb White, Hi Deb,
I think referencing a helper column may be the easiest way to do this, as Mark suggested.
In your source sheet create a new column, within that column type in the formula
=IFERROR(IF(AND(YEAR(TODAY()) = YEAR([Target / Actual Close]@row)), 1, 0), 0)
What this does is takes a look at your Target / Actual Close column and verifies if the year matches what the year of today's date is then kicks out the number 1 which you can change to say "Current Year", or "Yes".. I just use the 1 in this place so that I can easily reference this in a cross-sheet formula. Once you have done that in Row 1 you can right-click and choose 'Convert to Column Formula' so that all the rows will be populated appropriately.
From here you will change your formula as follows: =COUNTIFS({Business Unit}, [Business Unit]1, {Stage}, Stage$9, {Target / Actual Close}, 1)
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!