Help with a Countifs formula with conditions

Help with a Countifs formula with conditions

We have a sheet that holds information populated by a form. The form allows people to request the data team create a report for them. Sometimes the requests are actually looking for a report that would be regenerated every month. The initial request auto populates a report "DR" number in column 1. If the report is to be repeated, we manually add a new line to the collection sheet and an "REC" number is manually entered in column 2. (The sheet still creates a new "DR" number on that line).

Likewise there is a status column for the DR requests and a separate status column for the REC requests.

We also have an auto-generated column for date and time request was made, Date needed by column(generated by the form). Then there is a Date Completed column generated by the analyst. Finally, columns for our response time, requester expectation, and met expectation that populated by formulas.

We would like to calculate how many times we met expectation, didn't meet expectation, were on time, and open reports

So far we have tried COUNTIFS formulas with no luck, this being the most recent

=COUNTIFS({DATA Range 1}, @cell > 0, {STATUS Range 2}, NOT(@cell = "ongoing"))

Data Range 1 was looking at the Met expectation column and Range 2 was looking at the Recurring Status column. We also tried @row. The not ongoing was to differentiate from status of done.

I'm hoping someone can help with the formula or suggest another way to get the data we want. Wondering if we need to change our approach. Should we count the 2 different DR and REC numbers separately? So maybe "Count rows with a DR number but not an REC number and whose Met expectation column is greater than 0" and then "Count rows with an REC number and whose Met expectation column is greater than 0" ? Either way, I'm not very good at turning words into a formula so would appreciate any help/thoughts you can give.

Many thanks for your time thinking about this!!!

Answers

  • Hi @Mommaduck

    To adjust your first formula, try this:

    =COUNTIFS({DATA Range 1}, > 0, {STATUS Range 2}, <> "ongoing")


    You could add in more criteria if you want, such as only counting rows with a DR number:

    =COUNTIFS({DATA Range 1}, > 0, {STATUS Range 2}, <> "ongoing", {DR Number column}, <> "")

    (that says not blank)

    To count the REC number you would just swap out the DR Number Column range to be the REC number column range. (Make sure that if you're copying/pasting the formula you delete out the column reference and add an entirely new one in, instead of editing the reference which would change it in your other formulas as well).


    Here are some articles that I used: COUNTIFS Function / Cross Sheet References / Using <> to indicate "not"

    Let me know if I can clarify anything or help further!

    Cheers,

    Genevieve

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    If @Genevieve P's solution does not work...


    Are you getting an unexpected number from the COUNTIFS, or are you getting an error message? If you are getting an error message, which one are you getting?


    What is the formula used in your [Met Expectations] column?

  • Thanks for responding! It comes up Unparseable. Genevieve's came up Invalid ref.

    I tried switching it around to a nested formula - but all I know of nested formulas is what they just did in a webinar and I'm not a data analyst! This is what I tried but still got Unparseable. Same when I made it Countifs

    =COUNTIF({Met Expect})@row>0,COUNTIF({Recurring Request Number}@row, "Blank"

    Thoughts???

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    How are you creating your cross sheet references? Are you clicking on the small link in the help box when typing a formula that says to reference another sheet and selecting your ranges that way?


    Incorrectly referencing another sheet would be my first guess as to why Genevieve's solution is coming up with the invalid reference error.

    Give Genevieve's another shot, but make sure you are building your references properly and selecting the proper ranges from the other sheet.

  • Yes-it's the only way I know! Any thoughts on the nested formula idea??

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Are you able to provide a screenshot with Genevieve's solution in place? This way we can see the exact formula in place. Genevieve's solution should be working for you, but there are a number of factors that could throw an error even when the formula looks correct.

  • These are the most recent ones that have been tried. I had 2 Pro desk calls with one rep and a 3rd with someone else. The last rep suggested the community. Do work at SS to be able to access the content of those calls?

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I do not work for Smartsheet. I only darn near live in it. Haha.


    Let's check the source data. What type of data is in each of your ranges on the source sheet? Are there any errors within those ranges on the source sheet? Are your ranges on the same sheet as each other? Are your ranges the same exact dimensions as each other?


    If we can't narrow it down from the above, then the only other thing that could be wrong would be the cross sheet references themselves.

    Make sure you are following these exact steps to create your ranges...

    Start typing the formula.

    =COUNTIFS(


    In the helper box, click on the link.


    Select your source sheet from the list on the left, then select your first range. Then click the blue button in the bottom left corner to "Insert Reference".


    Your formula should now look like this:


    Continue typing the formula and create the references as needed following the above steps.

  • Hi, I'm back!

    I agree with Paul's last comment - it sounds like there's something going on with one of the two references in this:

    =COUNTIFS({DATA Range 1}, > 0, {STATUS Range 2}, <> "ongoing")

    It would be useful to see the window Paul has a picture of, where the column is highlighted for each reference.


    What exactly are {DATA Range 1} and {STATUS Range 2} in the other sheet?

  • Thank you both SO much for helping to trouble shoot! I'm not a data person. I'm an Admin so formulas are not my strength! And as a company, we are still fairly new with SS. Here are some screen shots that hopefully will help illustrate what's going on. ANY cooments you have-including sheet setup-would be gratefully received!!

    I redid my references as you suggested but it came back with an answer of 1 which I know is wrong. My best guesses are:

    -We are not entering our data correctly on the sheet AND the sheet was not set-up in the best way (I'd be the guilty party there!) Request # auto-populates when folks complete a form requesting a report. Date of request also comes from that form. Recurring Request # is generated by the analyst when a report request is actually going to need to be repeated on a regular basis. (They copy the original request line and paste it in on a new line.) The number is REC for recurring and then the digits from the original DR number connected to the first request. Analysts are SUPPOSED to delete the original date needed by and date completed on the recurring line but as you can see, that doesn't always happen.

    • -Is the current formula using the best columns?? What if we counted Met expectation only if Recurring Request # is empty - and then to capture the recurring requests, count met expectation if Recurring request number is NOT blank?
    • Again, many thanks for any and all help!!


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    How is the data entered into the [Met Expectations] column?


    Also, if I may request another screenshot...


    Open up the formula cell as if you are going to edit it then click inside one of the references so that your cursor is between the curly brackets. If the link in the help box says "Edit Reference", click on that then take a snippet of the reference selection highlighted.


    Can you do that for both of your references?

  • I agree with Paul's request above... I'm also wondering if since we're looking for not Ongoing we should also specify that we do actually want to count blank cells as well? It's my understanding that a COUNTIFS would skip blank cells.

    What about trying this:

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

    This should count the rows where the "Met Expectation" column is greater than 0 and the "Recurring Status" column is not "Ongoing", or if it is blank.

    Let me know if this gives you a different count than 1!

  • Met expectations is a formula generated column

    Above is from the formula that returned a 1

    This is where it's pointing to-sorry I can't do the whole dialog box-it shows data on the sheet that is HIPPA

    For the 2nd reference

    I am noticing that when I go to the column and mark it as the spot for the reference, the naming area is autopopulating to these names. I had used the Met Expect previously on the metric sheet. I did not enter it new when setting this formula. Does that mean anything??

    Genieve's suggested formula change. It brought back a 0

    I'm very grateful both of you are as stubborn as I am about finding a way to make this work!

  • Hmm for the formula change, it looks like you're missing the OR:

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

    Would you mind trying again?


    As long as the {Met Expect} reference is meant to point to the same column in that other formula, re-using it by copy/pasting should be fine.

  • Am I still missing a part?? Tried OR not in caps as well and got same message. There's something to be said for the brevity of Excel formulas.

  • 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 = ""))

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

  • 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

  • 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 NewcomePaul 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.

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

  • 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 NewcomePaul 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.

  • @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 NewcomePaul 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.

  • 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 NewcomePaul 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.

  • 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 NewcomePaul 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.

  • Oh of course!!! Good catch, Paul.

Sign In or Register to comment.