Help with a Countifs formula with conditions

2»

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    My apologies! I wrote the formula wrong when I threw in the "OR" ... last try?

    =COUNTIFS({Met Expect}, >0, {Recurring Status}, OR(@cell <> "Ongoing", @cell = ""))

  • Mommaduck
    Mommaduck ✭✭✭✭

    OMG!!! I think you did it!!!! These are all the formulas to find all the met expectations that I used

    Exceeded =COUNTIFS({Met Expect},

    >0, {Recurring Status}, OR(@cell <>

    "Ongoing", @cell = ""))

    Late =COUNTIFS({Met Expect},

    <0, {Recurring Status}, OR(@cell <>

    "Ongoing", @cell = ""))

    On time =COUNTIFS({Met Expect}, =0,

    {Recurring Status}, OR(@cell <>

    "Ongoing", @cell = ""))

    Open  =COUNTIFS({Met Expect},

    ="", {Recurring Status}, OR(@cell <>

    "Ongoing", @cell = ""))


    and this is what they returned

    It leaves 21 rows from the sheet unaccounted for that I'm guessing has something to do with data entry errors-ugh! Guess I'll start looking for those but at least we have a working formula??? Would you agree?? I'll get one of the analysts who is good with Excel to export and check these numbers that way. I can not thank you enough for getting us this far!!! I think SS needs to hire you!!

  • Genevieve P.
    Genevieve P. Employee Admin

    Ah yes! It looks like that has worked! Well done.

    You might be able to use a filter to find those 21 rows and see what's going on there.

    I'm happy to help! I actually am a Smartsheet employee, but I'll tell my boss about your kind words, haha, if you don't tell her that I initially wrote the formula wrong. 🙂

    Let me know if you have any other questions!

    Cheers,

    Genevieve

  • Mommaduck
    Mommaduck ✭✭✭✭

    Mums the word!! I'll try to figure out a filter (first one I tried didn't work ) When I finally finish this I get to move to another sheet where we need to subtract time including days. Support gave me a formula that is totally!! foreign to me so I may come back to you!!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Genevieve P Sometimes COUNTIFS will count blanks and other times it won't. I haven't tested it enough to be able to establish a pattern or "rules" surrounding when it does vs when it doesn't. Well done on finding the solution though.


    @Mommaduck Finally a working solution!!


    The filter may be a little tough to build, but you can actually modify each of your formulas to evaluate the data on a row by row basis on your source sheet, plug them into a checkbox column to check the box if it meets the criteria you have built into your formulas then filter based on your checkbox column to show rows that are unchecked.


    Here are your currents:

    Exceeded =COUNTIFS({Met Expect}, >0, {Recurring Status}, OR(@cell <> "Ongoing", @cell = ""))

    Late =COUNTIFS({Met Expect}, <0, {Recurring Status}, OR(@cell <> "Ongoing", @cell = ""))

    On time =COUNTIFS({Met Expect}, =0, {Recurring Status}, OR(@cell <> "Ongoing", @cell = ""))

    Open  =COUNTIFS({Met Expect}, ="", {Recurring Status}, OR(@cell <> "Ongoing", @cell = ""))


    If you put them on your source sheet as an IF to evaluate each row, they would look something like this...

    Exceeded =IF(AND([Met expectation]@row > 0, OR([Recurring Status]@row <> "Ongoing", [Recurring Status]@row = "")), 1)

    Late =IF(AND([Met expectation]@row < 0, OR([Recurring Status]@row <> "Ongoing", [Recurring Status]@row = "")), 1)

    On time =IF(AND([Met expectation]@row = 0, OR([Recurring Status]@row <> "Ongoing", [Recurring Status]@row = "")), 1)

    Open =IF(AND([Met expectation]@row = "", OR([Recurring Status]@row <> "Ongoing", [Recurring Status]@row = "")), 1)


    You could put each of those in a checkbox column and build your filter to show rows where all of those columns are unchecked. You can then hide those columns and turn the filter off (but save it) so that you have it in place for regular maintenance to check for bad data.

  • Mommaduck
    Mommaduck ✭✭✭✭

    I'm back. So as I tried to figure out the odd number that seemed to be missing (because I was thinking there should be a count for every line on the sheet), I realized a major flaw with my thinking. We really should only be counting the Met Expectations for lines where BOTH recurring status says 'done" AND status says done.

    Right now, Requester expectations populates by formula from Request date-date needed - so it populates as soon as the request is made via form that populates the sheet, not when it's done.

    Our Response Time is generated by formula Date Needed-Date Completed

    Met Expectation is generated by formula Requester Expect-Our Response time ...so since requester expectations has a value, it creates a value in met expectation even if the project is not done.

    See the screen shot below for an example

    This was the final formula we used that worked

    =COUNTIFS({Met Expect}, >0, {Recurring Status}, OR(@cell <> "Ongoing", @cell = "")

    I'm assuming I need to add something at the beginning of the formula to say if status says done? This was what I tried but got Unparseable

    =COUNTIFS({Status1}"Done", {Met Expect}, >0, {Recurring Status}, OR(@cell <> "Ongoing", @cell = ""))

    Clearly I'm missing something! I inserted the {Status1} using the edit reference tool. Do I need an @ cell? is done in quotation marks not the correct way to say only count the dones?

    How do you both do this all day and not have your head explode?!! The data analysts I support keep laughing and telling me they are bringing me over to the dark side of geek-dom but I'm not so sure!

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Mommaduck

    You're well on your way to geek-dom!! All you're missing is one small little comma after that first range....

    Try this:

    =COUNTIFS({Status1}, "Done", {Met Expect}, >0, {Recurring Status}, OR(@cell <> "Ongoing", @cell = ""))


    COUNTIFS require a {range} comma "Criteria" - does that make sense?

    You will also want to make sure you insert new references when referencing an entirely different column in the other sheet. (Versus clicking edit reference). If you Edit a current reference it will change this in all of the other formulas on your sheet that have the same {words in here}.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    HAHAHAHA!!


    @Mommaduck Smart thinking. You are definitely on your way. You may want to go ahead and start preparing for an increased caffeine intake if you plan on joining us down this particular rabbit hole though. Haha.

    How do we do it all day without our heads exploding? Well I can't speak for Genevieve, but my mother has always said I have a pretty thick skull. Maybe that has something to do with it? Or maybe it has to do with frequently beating my head against a wall which helps push things back in place. 😜


    @Genevieve P is correct though. Make sure you are creating a NEW reference when you do something like that because "Edit" reference will update all of those references within the sheet.

  • Mommaduck
    Mommaduck ✭✭✭✭

    @Paul Newcome , @Genevieve P I'm back - to having my head swarming!!!! I am confident all the formulas work but I have to admit I've forgotten what the formula is saying (in non-formula English!) - which is the only way it makes sense to me

    WE USED =COUNTIFS({Status1}, "Done", {Met Expect}, >0, {Recurring Status}, OR(@cell <> "Ongoing", @cell = ""))

    In my head it says "Count if status says done and met expectation is greater than 0 and recurring status is..." -and then I get foggy! What is OR? and the <> after that? The rest I believe was saying "if Recurring status didn't say Ongoing or the cell wasn't blank"

    REASON I AM ASKING: One of the sheet users-God Bless her soul!- actually thought about the clean up I asked them to do around Recurring requests and thinks there is a problem with how they count into met expectations-and I'm pretty confident she's right. I suspect the real problem is our sheet set-up and method of adding recurring requests is flawed. She's thinking simple go around is don't count REC#'s with the others. Count them separate.

    Recurring requests (REC#) are report requests that have to be generated multiple times vs a Request (DR#) that is only done once. Currently when the analyst realizes this need for repetition, they copy the original line and paste it as a new line on our sheet which generates a new DR# and they manually add REC# using the digits from the first DR#.

    I have been telling them to delete Date needed and completed entries on that line because of subsequent formulas on the sheet BUT as you can see, it still produces a 0 in Met Expectation and so would end up counted as an on time.

    I tried manually going into all the REC# lines and deleting all data and formulas from Date needed column to Met expectation column (essentially eliminating the false 0) expecting that would change the formula counts - but it didn't. (I saved the source sheet changes and refreshed the metric sheet)

    And that was what brought me back to the formula. I thought take out everything after the 0 and add another condition of not counting any rows with an REC# but how I wrote it didn't work {Recurring Request#}, ""

    Since Recurring Requests are always the problem, I'm debating on suggesting to my boss that we keep all REC requests on a separate sheet??? OR

    Do I change the very simplistic formulas in Our response time column thru met expectation to include a condition of not calculating if there is an REC#?

    My father always said a little knowledge is dangerous...he was right. A degree in Data Analytics would have been much more helpful than my degree in Theology and Secondary ed!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    "if Recurring status didn't say Ongoing or the cell wasn't blank"

    ^^ That is correct.


    As for the rest... It may be easier to help if we could access the sheet. Are you able to "Save as New", publish as Edit by Anyone, then post the published link here? That would definitely make it much easier to really see how everything is working together.


    P.S. The only "Degree" I have is my deodorant. Haha. Just goes to show that with practice and patience (and sometimes a little bit of guidance) this too can be learned.

  • Mommaduck
    Mommaduck ✭✭✭✭

    Don't I wish I could just share sheet!!! Unfortunately we are a company that works with HIPPA info so I can't. I may just make a copy and change the names to protect the innocent tho as your help has been SO VERY INVALUABLE!!!! I'll get back to you!

    Keep up the sense of humor! It's the best defense in these times!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I understand completely. We have to be very careful where I work what gets published and what gets shared due to sensitive/confidential info.


    If you make a copy and change the HIPPA data to "dummy data" (we usually use Donald Duck, Mickey Mouse, Santa Clause, etc), then a published link would work just fine.


    We would really only need a handful of rows of data that gives a sampling of each of the different scenarios that need to be accounted for.


    If you are actually able to do that, then I would also ask that you go ahead and include a couple of text/number columns, a couple of date columns, and a couple of checkbox columns for testing.


    If you are not able to publish even with "dummy data" entered, please let me know, and maybe we can find a way for you to walk me through a generic build that I can test with on my own.

  • Genevieve P.
    Genevieve P. Employee Admin

    I agree with what Paul said above! However just piping in for what the formula says for this bit:

    {Recurring Status}, OR(@cell <> "Ongoing", @cell = ""))

    This says:

    {Recurring Status} is NOT "Ongoing"

    OR

    {Recurring Status} IS blank

    So it's including the blank cells in the count. If you want to exclude blank cells, you just need to swap out the = for <> (the "is" for the "is not")

    =COUNTIFS({Status1}, "Done", {Met Expect}, >0, {Recurring Status}, OR(@cell <> "Ongoing", @cell <> ""))


    Then if you wanted to add in counting only if the Recurring Request is blank, your addition of {Recurring Request#}, "" should have worked:

    =COUNTIFS({Status1}, "Done", {Met Expect}, >0, {Recurring Request#}, "", {Recurring Status}, OR(@cell <> "Ongoing", @cell = ""))


    (This formula is still counting if the Status is anything but "Ongoing", including blanks). Let me know if this works! If not, some sort of dummy data would be great to test on.

    Thanks,

    Genevieve

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I am going to actually recommend against this:

    OR(@cell <> "Ongoing", @cell <> "")

    Here's why...

    When using an OR function, only one must be true. That means "Ongoing" is going to trigger a true for <> "" and end up getting counted. To count cells that are not "Ongoing" and not blank, I suggest switching it to an AND.

    AND(@cell <> "Ongoing", @cell <> "")


    I do see that somewhere along the way we dropped the {Recurring Request#} portion, so I wonder if adding that back in to match @Genevieve P's second formula above would work.

  • Genevieve P.
    Genevieve P. Employee Admin

    Oh of course!!! Good catch, Paul.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!