Trying to calculate the number of submissions

Cayla Davis

We just ran a survey in Smartsheet using forms, and its going through the next 2 weeks, i want to gather how many we have received from our survey document and display it on my metrics, to then display it on a dashboard. What formula would I use to do that? I was doing some research but i just keep getting #UNPARSEABLE

AKnight

@Cayla Davis Either! Whatever is standard for your organization. I always suggest using the sheet summary where the data came from to make it easier to write the formulas and personally I find it easier especially when the data is being presented elsewhere.

If you do house the formula in a different sheet you will have to set up a cross-sheet reference and you can use the formula =COUNT({Numbering Column from Canada Sheet}).

Some additional information to help on your smartsheet journey:

• The {} type brackets indicate that it is a reference from a different sheet, the [] brackets are calling to columns that exist in that sheet.
• Something to consider when developing this is how the user will be interacting with the data. If it is just a dashboard and they don't look at the source sheet, then you can probably store the metrics in that summary section on that sheet to reduce the number of sheets you need to create.
• Another thing to consider is what widget is being used, charts will require a sheet or report creation while metrics just need a cell to call to meaning you can put it in on the sheet itself.
• I really only do sheet creation as a last resort because of the time it takes to create and to avoid over-cluttering folders. First I see if it can be a helper column that can be hidden, if its a metric I put it in the sheet summary, if the data already exists and I am not making a fancy chart, I make reports.

Let me know if any of that doesn't make sense. The additional info has a lot of personal preferences and suggestions I make to companies but someone else might differently do it. Remember there are usually always multiple ways to get a correct answer.

AKnight

Hello again @Cayla Davis !

Is this counting every row in one sheet or is this counting rows across multiple sheets? Also is there anything specific you want to be counting from those rows (maybe ones tagged by certain users or a certain response)?

Cayla Davis

Hi @AKnight !!! It would be counting each row - right now i have the row setup as a numbering so its counting each item that gets added but i need it to continuously display each time on my dashboard - and its just one sheet that i need to pull from

AKnight

@Cayla Davis - because it is just one sheet, I suggest using the Sheet Summary (if your plan allows it). You can store your formula there away from the viewing section to keep the sheet clean. You can then use the Metric Widget on the dashboard and pull from the sheet summary data.

As for the formula, you can use any row range with the COUNT function. Because you are not filtering by any criteria, you won't need any IF statements added. I suggest using a column range that is automatically populated during form submission like: created by, created date, or auto-numbering. It sounded like you already have an auto-numbering column named "numbering" so you can use the following formula:

=COUNT([Numbering]:[Numbering])

Hope that helps!

Cayla Davis

@AKnight where would i put that formula? in my metrics sheet or would that go in my survey results sheet?

=COUNT([Numbering]:[Numbering])

AKnight

@Cayla Davis Either! Whatever is standard for your organization. I always suggest using the sheet summary where the data came from to make it easier to write the formulas and personally I find it easier especially when the data is being presented elsewhere.

If you do house the formula in a different sheet you will have to set up a cross-sheet reference and you can use the formula =COUNT({Numbering Column from Canada Sheet}).

Some additional information to help on your smartsheet journey:

• The {} type brackets indicate that it is a reference from a different sheet, the [] brackets are calling to columns that exist in that sheet.
• Something to consider when developing this is how the user will be interacting with the data. If it is just a dashboard and they don't look at the source sheet, then you can probably store the metrics in that summary section on that sheet to reduce the number of sheets you need to create.
• Another thing to consider is what widget is being used, charts will require a sheet or report creation while metrics just need a cell to call to meaning you can put it in on the sheet itself.
• I really only do sheet creation as a last resort because of the time it takes to create and to avoid over-cluttering folders. First I see if it can be a helper column that can be hidden, if its a metric I put it in the sheet summary, if the data already exists and I am not making a fancy chart, I make reports.

Let me know if any of that doesn't make sense. The additional info has a lot of personal preferences and suggestions I make to companies but someone else might differently do it. Remember there are usually always multiple ways to get a correct answer.

Cayla Davis

@AKnight that worked like a charm! I appreciate the detail!! i've been doing alot of dashboards lately to measure productivity and progressions, so this is helpful! im slowly creating my own user guide. I need you on speed dial! haha

THANK YOU AGAIN!!!!!! ❤️

AKnight

Glad it worked! Good luck with your new developments!!