Help with a Countifs formula with conditions

Mommaduck

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

Genevieve P.

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 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?

Mommaduck

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

Mommaduck

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

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

Mommaduck

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

Genevieve P.

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?

Mommaduck

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 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?

Genevieve P.

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!

Mommaduck

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!

Genevieve P.

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.

Mommaduck

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.