Fix my Formula

Kelly Gabel

Can someone please tell me what I'm missing here? I'm trying to count the number of records where "Stage 1" finish date is in the past and "Stage 2" finish date is in the future. I'm not getting any syntax errors and each piece of this formula works on it's own, so I must be missing something in the connection between the two.

Thank you! Tagging my formula guru @Paul Newcome for visibility!

=COUNTIFS({Program Schedule - Shell Mobility Task}, "Stage 1", {Program Schedule - Shell Mobility Finish}, <TODAY(), {Program Schedule - Shell Mobility Task}, "Stage 2", {Program Schedule - Shell Mobility Finish},

Kevin Smith

Hi Kelly

It looks like you are on the right path with the formula. Is there more? What you pasted is incomplete and would throw a #UNPARSEABLE. You would need a condition after Finish}, and then a ) to close out he syntax.

Send the rest if you have it.

I hope that helps.

Kelly Drake

I assume it is giving you a zero as a result because your formula is trying to count the same cell as having both "Stage 1" AND "Stage 2" in it at the same time. If the column is not a multi-select dropdown, this is not possible. And if it is a multi-select, I've always run into issues using simple countifs without nesting a HAS, FIND, or CONTAINS into it.

Kelly Gabel

Apologies, my formula didn't paste in completely. The full formula is below. I am getting a 0 as a result. This is not looking at the same cell. There is a Task column where one row will say Stage 1 and one will say Stage 2. Each of those rows have a corresponding Finish column and I'm basing on those dates.

=COUNTIFS({Program Schedule - Shell Mobility Task}, "Stage 1", {Program Schedule - Shell Mobility Finish}, <TODAY(), {Program Schedule - Shell Mobility Task}, "Stage 2", {Program Schedule - Shell Mobility Finish}, >TODAY())

Kevin Smith

I think you are wanting (Stage 1 and < Today) OR (Stage 2 and '>'Today) is that correct? So in you example you are looking for the result of '2'?

You may need to set up different COUNTIFS and then add them up. I believe it is looking at these as all AND's.

Kelly's suggestion of CONTAINS seems worth trying if you don't want to have two COUNTIFS.

If you just use:

=COUNTIFS({Program Schedule - Shell Mobility Task}, "Stage 1", {Program Schedule - Shell Mobility Finish}, <TODAY()) you get = 1

likewise with

=COUNTIFS({Program Schedule - Shell Mobility Task}, "Stage 2", {Program Schedule - Shell Mobility Finish}, '>'Today()) you get = 1.

Try that and see if that works for you.

Kelly Gabel

Kevin,

I don't think that doing two COUNTIFS and adding them up would get me to the right number. I'm looking for a count of records between those two dates. If one of my formulas just looks for every Stage 2 with a future date, I would be double-counting because everything in Stage 1 would have a future date for Stage 2 as well. I'm trying to get to the count of records that are actively in Stage 2, so they have already completed Stage 1.

Does that make sense? My brain is starting to turn a bit mushy with all of this formula work! LOL

I'm also not sure that Kelly's suggestion of CONTAINS works either because these formulas are not looking at the same cell for data. If you look at the screenshot I shared, there are two completely separate cells I am referencing.

Kevin Smith

LOL, I am there with you, it's Friday!!

But just wondering, wouldn't every date other than Today count with ( <Today and '>'Today)?

I am assuming when you have {Program Schedule - Shell Mobility Task}, that is a range of the whole column from the Program Schedule. So it looks down that col for Stage 1 AND also for Stage 2

So the formula :: =COUNTIFS({Program Schedule - Shell Mobility Task}, "Stage 1", {Program Schedule - Shell Mobility Task}, "Stage 2") Will return 0.

Tell me if this is true:

1) Count when Stage 1 is in the past. (Or should it be when %comp is 100?)

2) Count when Stage 2 is Active. (either Finish is in the future or %comp is less than 100)

So in your example is the count you are looking for is "1" or are you looking for a count of "2"?

Also, curious if you are looking for a count or if you just want to know when Stage 2 should be active.

Sorry for so many questions.

Kelly Gabel

Yes and fortunately my day/week is almost over!! Hope yours is too!

So, I'm not even sure if I'm understanding your questions right now! LOL

Maybe it will help if I say that I'm looking for the count of records where Today is between Stage 1's Finish and Stage 2's Finish.

Ideally, the % complete column would come into play as well, but I didn't want to complicate it even further...but now I'm thinking, maybe my formula shouldn't be looking at the date(s) at all...just the 100% column!

I'm going to try going down that road!

Kelly Gabel

Kevin,

I'm getting closer! Thank you for being a sounding board and giving me great food for thought!

I'm gonna call it a week at this point. I hope you have a great weekend!

Paul Newcome

Apologies for the delay. Things have been rather hectic for me lately.

The issue is that your formula is technically looking for "Stage 1" and "Stage 2" populated in the same cell at the same time.

The COUNTIFS function will count how many rows it finds a match to all ranges/criteria across a single row. This means you would need to have both stage 1 and stage 2 in the same row to get an accurate count from the COUNTIFS.

You may not want it to look at the same cell, but that is how it works.

My suggestion:

Insert two helper columns.

The first would be a text/number and would replicate the parent data in each row. Exactly how to do that depends on the structure of your sheet. It looks like Initiation and Execution share a parent. That is the data you want to pull. Something like this should work:

=IF(LEFT([Task Name]@row, 5) = "Stage", INDEX(ANCESTORS(), COUNT(ANCESTORS()) - 2))

Once you get the parent data on the rows, you will set up your second helper column (date type) with something like this...

=IF([Task Name]@row = "Stage 1", INDEX(COLLECT([Finish Date]:[Finish Date], [Task Name]:[Task Name], @cell = "Stage 2", [1st Helper]:[1st Helper], @cell = [1st Helper@row), 1))

Then your COUNTIFS would be like this...

=COUNTIFS({Program Schedule - Shell Mobility Finish}, @cell < TODAY(), {Date Helper}, @cell > TODAY())

Kelly Gabel

Thank you Paul! Hope the hectic stuff is all good!

I am going to work on the helper data...the problem is that my user has been maxing out the space on the sheets that I'm using to pull this count from so adding columns is a bit tricky. I really appreciate the explanation on why my formula wasn't working...makes total sense! If I'm able to add the helper data, I'm sure that will work. Thanks again!

Paul Newcome

Which limit(s) are you running into? Do you have any helper columns that could possibly be consolidated?

Kelly Gabel

the 500,000 cells per sheet. No, unfortunately, there aren't really many helper columns. The user decided to combine our Project Schedule with their customer info tracker and it made for a jam-packed sheet. That plus the fact that this program has hundreds of projects...why we keep hitting the limit.

In the future, I'll be advising against that type of tracker consolidation. 😂

Paul Newcome

Yeah. Consolidating multiple trackers can definitely be problematic. What is the possibility you could talk them into splitting them back out into two separate trackers then having them both accessible through a dashboard so they still only have to go to one place?

Kelly Gabel

Ya, I wish I could. At this point, we are so deep into the structure we have, that would take a massive effort. There is a possibility, with some additional support about to come on next week, that we might have some wiggle room there, but up until now, there has been on Project Manager drinking from this fire hose.

Paul Newcome

Ugh. Best of luck!