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

[GTT]

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.

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

Thank you,

 

[jasonb75]

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.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E4,ROW(E4:E100)-ROW(E4),0,1,1))*ISNUMBER(SEARCH(", "&TRANSPOSE(G1:G2)&",",", "&E4:E100&",")))

 

[GTT]

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]

What results did you get from testing the suggested formula?

 

[GTT]

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]

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]

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]

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.

=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]

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]

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.