Fix my Formula

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},


Kelly L. Gabel

Owner/Founder

Tech-Savvy Academy

kellylgabel.com

«1

Answers

  • Kevin Smith
    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.

    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

  • Kelly Drake
    Kelly Drake Overachievers Alumni

    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 Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| business optimization product manager

  • Kelly Gabel
    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())

    Kelly L. Gabel

    Owner/Founder

    Tech-Savvy Academy

    kellylgabel.com

  • Kevin Smith
    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.


    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

  • Kelly Gabel
    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.

    Kelly L. Gabel

    Owner/Founder

    Tech-Savvy Academy

    kellylgabel.com

  • Kevin Smith
    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.

    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

  • Kelly Gabel
    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 L. Gabel

    Owner/Founder

    Tech-Savvy Academy

    kellylgabel.com

  • Kelly Gabel
    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!

    Kelly L. Gabel

    Owner/Founder

    Tech-Savvy Academy

    kellylgabel.com

  • Paul Newcome
    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
    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!

    Kelly L. Gabel

    Owner/Founder

    Tech-Savvy Academy

    kellylgabel.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

  • Kelly Gabel
    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. 😂

    Kelly L. Gabel

    Owner/Founder

    Tech-Savvy Academy

    kellylgabel.com

  • Paul Newcome
    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
    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.

    Kelly L. Gabel

    Owner/Founder

    Tech-Savvy Academy

    kellylgabel.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!