Forum Discussion

Lien_Van_Vaerenbergh's avatar
Lien_Van_Vaerenbergh
Copper Contributor
Jun 20, 2024
Solved

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 dropdo...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jun 21, 2024

    Lien_Van_Vaerenbergh 

    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)

Lien_Van_Vaerenbergh's avatar
Lien_Van_Vaerenbergh
Copper Contributor
Jun 21, 2024

HansVogelaar 

 

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.

HansVogelaar's avatar
HansVogelaar
MVP
Jun 21, 2024

Lien_Van_Vaerenbergh 

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)