Formula for counting values from single-select drop down on a form

Saadia Ashraf

Hello,

I'm trying to figure out how i can count or which formula i can use to count how many people selected their desired choices from three different categories (single-select dropdown) that falls under 3 different groups. I created a form out of a sheet and my question is when people are going to pick their top 3 choices for each of those 3 categories what can i do or use to count those selections on the sheet. Let me know if you have any additional questions or if this all makes sense. I was thinking to use Count formula but then I am thinking do i have to put that formula under each column (9 columns for 3 categories).Ex:

1-    Goals for the next 1-3 months:

a)     First Choice: (Single-select dropdown)

b)    Second Choice: (Single-select dropdown)

c)     Third Choice: (Single-select dropdown)

2-    Goals for the next 3-6 months:

a.     First Choice: (Single-select dropdown)

b.     Second Choice: (Single-select dropdown)

c.     Third Choice: (Single-select dropdown)

3-    Goals for the next 9-12 months:

a.     First Choice: (Single-select dropdown)

b.     Second Choice: (Single-select dropdown)

c.     Third Choice: (Single-select dropdown)

Paul Newcome

There is a section in the Form Settings that allows you to have the form populate either the bottom or the top of the sheet.

Paul Newcome

What is the purpose for collecting these counts? Are you wanting to only count how many people made a selection, or do you want counts for specific selections? Are you able to provide a screenshot with some sample data input?

Saadia Ashraf

We want to know how many people selected which option/selection. so i wanted to know the number of people voted as well as the number of times they picked up a specific option.

Paul Newcome

How do you plan to display this data? Are you able to provide a screenshot with some sample data?

Saadia Ashraf

This is how i have created it (mock data) but the format is exactly the same i am following because based off of this format i have created the form.

2020-06-16_13-49-36.jpg

Saadia Ashraf

@Paul Newcome I double-checked I want to use the formulas to count up how many votes each option gets. I guess this will give you more clarity. I hope i am on a right track

Paul Newcome

The easiest way to do this will be to create two more columns (or a metrics sheet or Sheet Summary fields). I'll give an example of using two additional columns on the same sheet.

Option..........Count

Option 1...........f

Option 2...........f

Option 3...........f

Option 4...........f

Then the formula where you see f would be:

=COUNTIFS([First Option Column]:[Last Option Column], Option@row)

Saadia Ashraf

@Paul Newcome Thank you for your help. I am still trying to get my head around this. So you mean for all the columns i have in there (9 columns in total) i just have to create two additional columns -Options and Count and go from there? Correct?

Paul Newcome

That is correct. List your different options down one column and then use the formula to count them in another column.

Saadia Ashraf

This is what i have come up with:

=COUNTIFS([First Choice (1-3 Months)]:[Third Choice (6-9 Months)], Option 1)

The "Option" in the end indicates the column 'Option" where i have listed down all the options we have from the three time frames and then the row. Am i doing something wrong here.

Paul Newcome

Use "Option@row" without the quotes.

=COUNTIFS([First Choice (1-3 Months)]:[Third Choice (6-9 Months)], Option@row)

Try that exactly as is.

Saadia Ashraf

It didnt work out for me i am pretty sure i am doing something wrong somewhere.

Also just to be sure, with every option the row number will change? I think i am definitely wrong somewhere.

Paul Newcome

Can you give a screenshot of how you have the table set up to also show the column names you are using?

Saadia Ashraf

@Paul Newcome I tried adding up the number for the row as well, nothing worked. Please note that i have made a form out of this sheet and when i will send them the form whatever selection they will make for each time frame-that is data i want to collect and this is why i m trying to come up with the formulas to count up how many votes each option gets.

Paul Newcome

Try moving your table to outside of the range. Right now you are including it in the counts because you are going from the [First Choice (1-3 Months) column on the left to the [Third Choice (9-12 Months)] on the right. Your table is right in the middle of the data you are evaluating.

Saadia Ashraf

I tried moving the two columns to the end and did it again-nothing works!