Counting Instances in Cell with Multiple Drop Down Select

Donna T

We have an internal communications plan. In one column we track the method of communication. Sometimes the same communication, represented as a row, goes out via multiple methods, and therefore multiple selections are made in the Delivery Method cell.

I am trying to create some metrics. When I try to countif where cell={method}, it seems to only count the first one. That is to say, based on the image below it will always count the "Email (one-off)", but then I can't get it to count the 2nd, 3rd, etc entries in the same cell with Email (one-off), even if defined in the formula.

I know how to workaround it, but was hoping to keep my spreadsheet simple (I didn't want to have a column for each method if I could avoid it).

Any suggestions?

Thank you!

Donna

Chris Mondeau

Hi Donna,

Apologies if I interpreted this wrong, but it looks like you want to count all rows that contain "Email (one-off)", "Social Media", etc.

If that's the case, then this might work. I'm using a countif function across the entire column range for the "Delivery Method" where the cell in that range contains "Email (one-off)". You can adjust the criteria to "Social Media" or any others in the other summary columns, but the formula should be the same.

And if you want to count the number of methods listed in the "Delivery Method" for one specific row, you can use the =COUNTM formula to count all the values from the mult-select.

Hopefully that helps!

Chris Mondeau

Hi Donna,

Apologies if I interpreted this wrong, but it looks like you want to count all rows that contain "Email (one-off)", "Social Media", etc.

If that's the case, then this might work. I'm using a countif function across the entire column range for the "Delivery Method" where the cell in that range contains "Email (one-off)". You can adjust the criteria to "Social Media" or any others in the other summary columns, but the formula should be the same.

And if you want to count the number of methods listed in the "Delivery Method" for one specific row, you can use the =COUNTM formula to count all the values from the mult-select.

Hopefully that helps!

Donna T

Thank you so much! I forget about those subcommands for formulas!