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

• ✭✭✭✭✭✭

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))`

• ✭✭✭✭✭✭

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))`

• ✭✭✭✭✭✭

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.

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!