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

GTT

New Member
Joined
May 3, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
13,148
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 3, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
13,148
Office Version
  1. 365
Platform
  1. Windows
What results did you get from testing the suggested formula?
 

GTT

New Member
Joined
May 3, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
13,148
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 3, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
13,148
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 
Solution

GTT

New Member
Joined
May 3, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
13,148
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top