Formula Help Part 2
Hi there,
I would like to count all of the projects in my portfolio that are launching in Jan, in Feb, in March, etc. I have a Date column called Upcoming Launch Date.
I posted previously and received this formula which works (January example):
=COUNTIFS([Upcoming Launch Date:Upcoming Launch Date], IFERROR(MONTH(@cell), 0) = 1)
What I later realized is that I would only like to count only those upcoming launch dates that are in January2019. I have projects for each month that launched last year that I do not need in my calculation.
Hoping someone in the community can help!
Thanks so much,
Laura
Comments
-
Mike Wilday ✭✭✭✭✭✭ -
Lchicklis ✭✭✭✭
Getting the Unparseable error
-
Mike Wilday ✭✭✭✭✭✭
-
Lchicklis ✭✭✭✭
Unfortunately this didn't work either. Any other suggestions? I really appreciate the help!
-
Mike Wilday ✭✭✭✭✭✭
(－‸ლ) I see what's missing. Sheesh. I need to look more carefully... the brackets need to surround each mention of the column name. -----> [Upcoming Launch Date]:[Upcoming Launch Date], Not just around both mentions... It should work now.
=COUNTIFS([Upcoming Launch Date]:[Upcoming Launch Date], IFERROR(MONTH(@cell), 0) = 1, [Upcoming Launch Date]:[Upcoming Launch Date], IFERROR(Year(@cell), 0) = 2019)
-
Lchicklis ✭✭✭✭
THANK YOU! Super helpful!
-
Mike Wilday ✭✭✭✭✭✭
Glad we got it working! Have a great day.
-
Paul Newcome ✭✭✭✭✭✭One thing I have found to be helpful with the COUNTIFS/SUMIFS functions is using an AND statement if you are referencing the same range for multiple criteria. Depending on the length and complexity of your original formula, it can help with shortening it and with organization if you have a lot of different ranges all in one formula.
Another way of writing your working formula would be...
=COUNTIFS([Upcoming Launch Date]:[Upcoming Launch Date], AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(Year(@cell), 0) = 2019))
While this doesn't make much of a difference with only one range reference, I have a few that reference 4 and 5 ranges (some of which require up to 3 sets of criteria each). It helps greatly with simplifying things and I personally forget less what criteria has been added and what is left to add.
Just a FYI for future reference.
P.S.
OR can be used the same way within the COUNTIFS/SUMIFS functions.
thinkspi.com
-
Mike Wilday ✭✭✭✭✭✭
Good suggestion. That definitely tidies things up and condenses them!
-
Paul Newcome ✭✭✭✭✭✭
Thanks Mike.
I have some tracking that looks at Projected Start, Actual Start, Projected Finish, and Actual Finish based on month and year (and in some cases week number and year) plus the Project Lead which could be one of three different people (or one of three other people if a different team).
I have so many different ranges looking at multiple criteria using cross sheet references that I had to find every way possible to keep the count of references as low as possible.
thinkspi.com
-
Mike Wilday ✭✭✭✭✭✭
Smart.
Help Article Resources
Categories
<\/p>
You can have a formula in AFO Version and make it a column formula. The formula would be \"=Right([Primary Column]@row, 4)\". This formula will pick up the last 4 characters of the text in your primary column.<\/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":106523,"type":"question","name":"SUMIF formula not working, can't see why...","excerpt":"I've been messing with this for an hour and going crazy. Can anyone see the problem? Or suggest an alternative formula? The formula =SUMIF({8. FY24 FSS Travel Tracking Total Cost}, Appropriation@row, {8. FY24 FSS Travel Tracking Log ARU}) It is located in the cell that's been circled. It is referencing columns on this…","categoryID":322,"dateInserted":"2023-06-15T19:58:37+00:00","dateUpdated":null,"dateLastComment":"2023-06-15T21:02:57+00:00","insertUserID":94351,"insertUser":{"userID":94351,"name":"Stephanie Allison","url":"https:\/\/community.smartsheet.com\/profile\/Stephanie%20Allison","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-15T20:59:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":94351,"lastUser":{"userID":94351,"name":"Stephanie Allison","url":"https:\/\/community.smartsheet.com\/profile\/Stephanie%20Allison","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-15T20:59:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":22,"score":null,"hot":3373723894,"url":"https:\/\/community.smartsheet.com\/discussion\/106523\/sumif-formula-not-working-cant-see-why","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106523\/sumif-formula-not-working-cant-see-why","format":"Rich","lastPost":{"discussionID":106523,"commentID":380931,"name":"Re: SUMIF formula not working, can't see why...","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380931#Comment_380931","dateInserted":"2023-06-15T21:02:57+00:00","insertUserID":94351,"insertUser":{"userID":94351,"name":"Stephanie Allison","url":"https:\/\/community.smartsheet.com\/profile\/Stephanie%20Allison","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-15T20:59:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/ONQHHA6AMB9R\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-15T21:02:05+00:00","dateAnswered":"2023-06-15T20:24:11+00:00","acceptedAnswers":[{"commentID":380897,"body":"
