# Count multiple items in a drop-down list with a single cell solution

#### GTT

##### New Member
Hello,

I am using this formula to count the text items selected in a column that contains a drop-down list in each cell with some cells containing multiple items selected in each drop down. I am using this format so it displays visible filtered results accurately.

This formula works but requires the addition of a helper column. I am looking for a single cell solution that does not require an additional column.

=SUBTOTAL(3,E4)*(LEN(E4)-LEN(SUBSTITUTE(E4,",",""))+1)
Then this column is summed to get the visible count.

Thank you,

#### jasonb75

##### Well-known Member
As @Peter_SSs has noted above, there are some details open to interpretation so this is only a quick attempt.

Where G1:G2 contain the criteria to include (or exclude).
In theory, for included criteria you should only need this formula. For excluded criteria subtract the result of this formula from the result of one of the earlier suggestions.
Excel Formula:
``=SUMPRODUCT(SUBTOTAL(3,OFFSET(E4,ROW(E4:E100)-ROW(E4),0,1,1))*ISNUMBER(SEARCH(", "&TRANSPOSE(G1:G2)&",",", "&E4:E100&",")))``

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### GTT

##### New Member
VBA is fine if instruction to implement is available. I have done some copying and pasting of code provided into the Visual Basic code modifying target columns to allow multiple entries in a drop down as an example.

Regarding your question, yes X and A would count as 2.

Thank you,

#### jasonb75

##### Well-known Member
What results did you get from testing the suggested formula?

#### GTT

##### New Member
What results did you get from testing the suggested formula?

Thank you for your reply. I don't follow what column G is or where to input in the formula what letters to include and what to exclude.

The original formula provided to me is great and tells me how many F & CFS where selected in total including counting each selection as 1 when multiple sections where chosen in a cell.

I then used the formula below to calculate how many where F:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E4,ROW(E4:E45)-ROW(E4),0,1,1))*(LEN(E4:E45)-LEN(SUBSTITUTE(E4:E45,",","F"))+(E4:E45<>"CFS")))

I then divided by the total to give me the percentage of F.

/SUMPRODUCT(SUBTOTAL(3,OFFSET(E4,ROW(E4:E45)-ROW(E4),0,1,1))*(LEN(E4:E45)-LEN(SUBSTITUTE(E4:E45,",",""))+(E4:E45<>"")))

I now would like to apply this to another column where there are 6 choices in a drop-down, so if I want to know how many F out of the total or how many F & S, I need to include multiple letters in (E4:E45,",","F") or exclude multiple letters in (E4:E45<>"CFS"). I just don't know how to include more than F in the formula above or exclude more than CFS.

Thank you

#### jasonb75

##### Well-known Member

I don't follow what column G is or where to input in the formula what letters to include and what to exclude.
As I said in the post with the formula
Where G1:G2 contain the criteria to include
You don't put the letters in the formula, you put them in G1:G2.

If you're trying to use the exclusion part to prevent "CFS" being counted as "F" in error, then you don't need to. The formula that I've suggested will not include partial matches.

#### GTT

##### New Member
As I said in the post with the formula

You don't put the letters in the formula, you put them in G1:G2.

If you're trying to use the exclusion part to prevent "CFS" being counted as "F" in error, then you don't need to. The formula that I've suggested will not include partial matches.

The formula works well. Thank you very much.

For my own understanding, regarding other formulas in general, how to reference (where to put commas, quotes or & sign) to include more than one item in a formula? Example: (E4:E45,",","F"). How to include/reference F & G not just F? Or to exclude F & G not just F (E4:E45<>"F").

Thank you,

#### jasonb75

##### Well-known Member

For my own understanding, regarding other formulas in general, how to reference (where to put commas, quotes or & sign) to include more than one item in a formula?
As a general rule, you would simply change "F" to {"F","G"} however there are exceptions.

With the formula that I suggested for your current task you would need to use a semicolon instead of a comma.
Excel Formula:
``=SUMPRODUCT(SUBTOTAL(3,OFFSET(E4,ROW(E4:E100)-ROW(E4),0,1,1))*ISNUMBER(SEARCH(", "&{"F";"G"}&",",", "&E4:E100&",")))``
The & symbol is only used to join things together. In the formula above it is used to add a comma and space to the start and end of the criteria and the range to search so that partial matches are not counted in error.

Although you are seeing a search for "F" or "G in "F, "CFS" the formula will be looking for ", F, " or ", G, " in ", F, CFS, " which prevents the F in CFS from being counted incorrectly.

#### GTT

##### New Member
This last formula works great enabling me to count one or more items (imputing them right in the formula, separated with a comma) and without using an additional reference cell(s).

Thank you,

#### jasonb75

##### Well-known Member
imputing them right in the formula, separated with a comma
If you have separated them with a comma then incorrect results are possible.

The syntax of the formula that I suggested (separated with a semicolon) is done in a very specific way to prevent errors in the results.

Replies
12
Views
170
Replies
5
Views
114
Replies
0
Views
163
Replies
0
Views
229
Replies
1
Views
244

1,136,320
Messages
5,675,059
Members
419,548
Latest member
wfarzand

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back