Formula Help Part 2

Lchicklis

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

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.