COUNT formula not working on form submissions due to row numbers changing

Dawn S

Hello, I am trying to use COUNT to keep a running /live tally of requests that have not been closed yet in a Form driven sheet.

Every time a customer enters a new request, row #1, is no longer row #1, and it breaks my formula, whereas, it will not include that new customer submission into the formula.

Locking the row does not help.

JamesB

@Dawn S Can you provide a copy of your formula. If it is written so that it can be a column formula, then it will not matter if row 1 changes.

Dawn S

Hi James, good thought, but I can't use the column formula because there are items (at the bottom of the sheet) that we are holding for follow up. All we want to count are items that are above a specific row. I have tried locking the rows to no avail as well.

JamesB

What if you use the sheet summary area to keep your formula?

Dawn S

Yes, that's what I am using, and interestingly, it still keeps changing my formula automatically even when i have the sheet summary formula set to "locked"

Dawn S

Smartsheet Form Error.png

here's what happens when a new order is submitted

DKazatsky2

Hi @Dawn S,

In your form settings, do you have it set to put new submissions at the top of the sheet? That may be causing this issue, or at least contributing.

Hope this helps,

Dave

Dawn S

Hi Dave, yes that's exactly what's causing it. But, the sheet is built that way on purpose because we use the bottom half of the sheet to track escalations - the only way it seems i could fix this is by moving the escalations off this sheet and to another sheet, but this sheet is supposed to be used as a one-stop-shop for our team to have visibility, so moving the escalations off of the sheet is not an option, either.

JamesB

@Dawn S Try adding a $ symbol to your formula.

=COUNT([New Order or Follow Up]$1:[New Order Follow Up]8)

Dawn S

thank you, James. I will try this right now!

Dawn S

nope, it did not work. It just continued to change my formula from $1 to $2

JamesB

@Dawn S Darn. Ok, I have one more thought up my sleeve. On the column for new order follow up, where you have your metrics, how many of those rows have data and it is different than the data at the top of the sheet? We can use a countifs to only count the data you care about and ignore the rest. With the countifs, we can remove the reliance on looking at only specific rows and instead have the formula look at every row in the column.

Dawn S

@JamesB James, I am so thankful for your time in looking at this with me. I agree, I was just thinking of using a COUNTIFS against two columns. So, for instance, say there is a new request that comes in, "New Order or Follow Up" will have data provided by the customer (and yes it's the same data that are in the escalation fields down below). BUT then at the same time, nobody has picked up the order/ticket yet, so then the "Customer Service Rep Working this Order" field will be blank. That would suffice as a "pending request" and give me the calculation I need.

So really, I need to decide the formula for IF new order or follow up is not blank, BUT Customer Service Rep IS blank.

JamesB

@Dawn S Based on your statement, this formula should give you the result you are looking for.

=COUNTIFS([New Order of Follow Up]:[New Order of Follow Up], <>"", [Customer Service Rep Working This Order]:[Customer Service Rep Working This Order], ="")

Dawn S

@JamesB oh my GOSH JAMES this worked!! Thank you so much, this is working like a charm!! Thank you SO MUCH.

Brilliant!

JamesB

@Dawn S Your Welcome. I am glad that we were able to work together to provide you a workable solution to your challenge. Have a great day.