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
-
Jenna Bailey ✭✭✭✭✭
@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
-
Mark Cronk ✭✭✭✭✭✭
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.
-
Deb White ✭✭✭✭
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
-
Mark Cronk ✭✭✭✭✭✭
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.
-
Jenna Bailey ✭✭✭✭✭
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),"")
-
Deb White ✭✭✭✭
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
珍娜,我知道我的标准业务单元和圣age 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.
-
Jenna Bailey ✭✭✭✭✭
@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)
-
Deb White ✭✭✭✭
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
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/73646/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":36,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"