Survey Result Dashboard COUNTIF CONTAINS formula

MikeChapNL

I created a survey (form) where guests of an event can rate location, food, keynote speakers etc. from 1 to 5 (1 being very bad and 5 being very good). To be able to create a widget in the dashboard that displays an average score I first created a stats sheet that collects the entries from the form sheet.

I couldn't get this in an all-in-one formula, I'm no Smartsheet expert and I suffer from LongCovid. Because the brain fog is terrible sometimes I gave up and tried a rookie approach by creating simple formulas in several colums of the stats sheet calculating the number of times a guest selected a value (5 Zeer goed, 4 Goed etc..). Zeer goed is Dutch for very good with this formula:
=COUNTIFS({751 Enquete Uitnodigingstraject}; ="5 Zeer goed")

I added a column next to it that calculates this outcome x the score, in this case 5. I do this for all 5 ratings (5,4,3,2,1) and created a total column hoping I could find a formula to devide the total score with the number of guest that filled out the survey.

So I therefor created an auto-number column in the survey result form sheet that displays a row-ID with a prefix. This way I was hoping to be able to calculate the number of rows by counting the prefix in this row-ID column and use this in the stats sheet by dividing the total score with the number of rows in the survey sheet.

This is where I have been stranded. Apparently, to count the identical prefix of the row ID (aka count the number of rows in the survey form sheet) this formula isn't working.
=COUNTIFS({751 Enquete Row ID}; CONTAINS("751-210524"))

But I'm also having trouble getting the right formula to be able to divide the total score with the number of rows in the survey sheet.

Help is much appreciated.

Lucas Rayala

Hi @MikeChapNL — to fix the COUNTIFS, update the formula to include the @cell reference and change the semi-colon to a comma (unless the semi-colon is a convention specific to the Netherlands instance of Smartsheet—in the States, we use a comma to separate the the fields). Anyway, you will need the @cell reference — let me know if it works with the semi-colon, just so I know! Here it is:

=COUNTIFS({751 Enquete Row ID}, CONTAINS("751-210524", @cell))

Lucas Rayala

Hi @MikeChapNL — to fix the COUNTIFS, update the formula to include the @cell reference and change the semi-colon to a comma (unless the semi-colon is a convention specific to the Netherlands instance of Smartsheet—in the States, we use a comma to separate the the fields). Anyway, you will need the @cell reference — let me know if it works with the semi-colon, just so I know! Here it is:

=COUNTIFS({751 Enquete Row ID}, CONTAINS("751-210524", @cell))

Lucas Rayala

Also, I'm sorry about the long-Covid issues! But I think it's a better practice to break up your formulas anyway. It's easier to fix things in the future, and other people will be able to figure out what you did.

MikeChapNL

Hi Lucas,
Thank you!!!!
Kind regards,
Michael