Obtaining an answer frequency (count) from a multi-answer cell (populated via form)

Susan Pattison

All, I'm really struggling!

I have a column, populated via a form, that allows for many choices of Activity the user engages in.

I want to count the total number of times an Activity choice is selected. ie, the frequency at which an activity is chosen for that user, (and utlitmately that user profile).

I've been trying countif, countif with has, countif with contains...nothing , I get 0 as the count

I'd like to have these calculation functions on a different smartsheet to the one that received the survey inputs.

My two sheets are

CTSA survey (where form data goes). the column with multiple activity responses from the form is called "Activity"

Activity Survey Summary (where i'd like to do all the counting!). The primary column of this sheet has the list of all Activity choices, one per cell listed in the first column.

Thank-you!

Bassam Khalil

Hi @Susan Pattison

Hope you are fine, i created a sample of your case please check the following link to the sample sheets

1- CTSA survey

https://app.smartsheet.com/b/publish?EQBCT=1e4f8f9e6ad944ea8d6d566702599210

2- Activity Survey Summary

https://app.smartsheet.com/b/publish?EQBCT=c7cd6c935d074428b1ccc16ce7a7b87b

try to use the following formula in your " Activity Survey Summary"

=COUNTIFS({CTSA survey User}, User1, {CTSA survey Activities}, [Primary Column]@row)

Susan Pattison

HI Bassam,

Unfortunately, I am still getting an error, specifically "Unparsable".

please see my sheets here

Survey Inputs

https://app.smartsheet.com/sheets/7Vv4vpxwPXxhH4qQX9qMcJpWJH9jjfHQXwX78x31?view=grid

Activity Summary

https://app.smartsheet.com/sheets/7Vv4vpxwPXxhH4qQX9qMcJpWJH9jjfHQXwX78x31?view=grid

I loved your "today" cell, how did you forma the cell to show the date?

Thank-you for your help!

ncanales

Can you show a snapshot of your screen with the formula?

Block any private/sensitive data.