I am trying to count the number of items in a popup menu. I've tried different things I've seen online but can't seem to get it to apply to my table.
I am trying to track which day misses the most often. Column A has their names (blocked out for obvious reasons), Column C is a popup menu showing the days (M, T, W, R, and F). I am collecting the data in ML Data 26-27 data. Is there a way to do this?
[Edited by Moderator]
If I read between the lines, you want a count of how many times each day is selected, right? So how many "M", "T", "W", etc...?
On that basis, you can get a list of unique values (which days are selected at all) via:
=UNIQUE(C)
This will return a list of which days are selected at all.
You can combine this with a COUNTMATCHES() to find how many times each day appears:
=COUNTIF(C,UNIQUE(C))
This takes the list of UNIQUE() days, and counts how many cells in column C match each entry.
Put these two formulas in adjacent cells and you'll get a synched list.
If you want to sort it (e.g. to filter the least selected day) you can extend the formula via something like:
=SORTBY(HSTACK(UNIQUE(C), COUNTIF(C, UNIQUE(C))), COUNTIF(C, UNIQUE(C)), −1)
This combines the two columns of day names and the frequency and sorts them on the frequency.
If I read between the lines, you want a count of how many times each day is selected, right? So how many "M", "T", "W", etc...?
On that basis, you can get a list of unique values (which days are selected at all) via:
=UNIQUE(C)
This will return a list of which days are selected at all.
You can combine this with a COUNTMATCHES() to find how many times each day appears:
=COUNTIF(C,UNIQUE(C))
This takes the list of UNIQUE() days, and counts how many cells in column C match each entry.
Put these two formulas in adjacent cells and you'll get a synched list.
If you want to sort it (e.g. to filter the least selected day) you can extend the formula via something like:
=SORTBY(HSTACK(UNIQUE(C), COUNTIF(C, UNIQUE(C))), COUNTIF(C, UNIQUE(C)), −1)
This combines the two columns of day names and the frequency and sorts them on the frequency.
Counting popup menu items in a table column