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 January 2019. 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

Find more posts tagged with

Formulas

Comments

Mike Wilday

Try: =COUNTIFS([Upcoming Launch Date:Upcoming Launch Date], IFERROR(MONTH(@cell), 0) = 1), [Upcoming Launch Date:Upcoming Launch Date], IFERROR(Year(@cell), 0) = 2019)

Lchicklis

Getting the Unparseable error

Mike Wilday

Oops. I see what I did. Try this one.

=COUNTIFS([Upcoming Launch Date:Upcoming Launch Date], IFERROR(MONTH(@cell), 0) = 1, [Upcoming Launch Date:Upcoming Launch Date], IFERROR(Year(@cell), 0) = 2019)

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.

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.

Mike Wilday

Smart.

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…