Forum Discussion
Count how many people worked on a task - Dropdown menu
Hi there,
I'm trying to wrap my head around the dropdown menu function in Excel.
I'm using it this year to track which tasks were done on how many days.
F.e. the picture below are all dropdown cells.
Per day I add tasks per zone / category / detail / how many people worked on it.
After two months I want to know:
How many people worked on the food fronts: how many days / how many people?
I'll add a filter on the column detail to search for the food fronts, but I want to be able to count the amount of people worked on it.
Does there exist a formula to count the amount of cells selected in one dropdown?
In this example the formula would say: 6 people worked on food fronts that day.
Many thanks!
Make sure that you use straight double quotes "", not curly quotes. And refer to E2 throughout:
=IF(E2="", 0, LEN(E2)-LEN(SUBSTITUTE(E2, CHAR(10), ""))+1)
And if you use comma as decimal separator, you should use semicolon ; between the arguments of the functions:
=IF(E2=""; 0; LEN(E2)-LEN(SUBSTITUTE(E2; CHAR(10); ""))+1)
Let's say the cell with the people who worked on it is D2.
The number of people is
=IF(D2="", 0, LEN(D2)-LEN(SUBSTITUTE(D2, CHAR(10), ""))+1)
This can be filled down.
Thank you so much for your quick reply.
I get the notificiation that there's something wrong with the formula.
The cell containing the names from a drop down is E2 in my case.
Make sure that you use straight double quotes "", not curly quotes. And refer to E2 throughout:
=IF(E2="", 0, LEN(E2)-LEN(SUBSTITUTE(E2, CHAR(10), ""))+1)
And if you use comma as decimal separator, you should use semicolon ; between the arguments of the functions:
=IF(E2=""; 0; LEN(E2)-LEN(SUBSTITUTE(E2; CHAR(10); ""))+1)
