Survey Result Dashboard COUNTIF CONTAINS formula
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 allinone 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 autonumber column in the survey result form sheet that displays a rowID with a prefix. This way I was hoping to be able to calculate the number of rows by counting the prefix in this rowID 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("751210524"))
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.
Best Answer

Hi @MikeChapNL — to fix the COUNTIFS, update the formula to include the @cell reference and change the semicolon to a comma (unless the semicolon 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 semicolon, just so I know! Here it is:
=COUNTIFS({751 Enquete Row ID}, CONTAINS("751210524", @cell))
Answers

Hi @MikeChapNL — to fix the COUNTIFS, update the formula to include the @cell reference and change the semicolon to a comma (unless the semicolon 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 semicolon, just so I know! Here it is:
=COUNTIFS({751 Enquete Row ID}, CONTAINS("751210524", @cell))

Also, I'm sorry about the longCovid 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.

Hi Lucas,
Thank you!!!!
Kind regards,
Michael
Help Article Resources
Categories
Check out the Formula Handbook template!