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]

You could try something like this for data in rows 4 to 100, adjust as needed.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E4,ROW(E4:E100)-ROW(E4),0,1,1))*(LEN(E4:E100)-LEN(SUBSTITUTE(E4:E100,",",""))+1))

Not sure that it will work without a data sample for testing.

 

[GTT]

Thank you for your reply. Unfortunately it is not producing the correct answer.

I have uploaded a sample image of what the column would look like. The correct answer in this example would be total of 11 ( 3 CFS and 8 Fs).

Thank you,

 

[jasonb75]

I've entered the same data into a sheet and it produces the correct answer. If I apply a filter to hide certain rows then the result changes as expected.

Note that if your visible blank cells contain formulas then they will be included in the count. If that is the case then you will need additional allowances to be made for that in the formula.

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

 

[Peter_SSs]

If you have the LET function, a slight variation to consider might also be

=LET(r,E4:E100,s,TEXTJOIN(",",1,FILTER(r,SUBTOTAL(3,OFFSET(E4,ROW(r)-ROW(E4),,1)))),LEN(s)-LEN(SUBSTITUTE(s,",",""))+1)

 

[GTT]

I've entered the same data into a sheet and it produces the correct answer. If I apply a filter to hide certain rows then the result changes as expected.

Note that if your visible blank cells contain formulas then they will be included in the count. If that is the case then you will need additional allowances to be made for that in the formula.

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

Thank you, this one works perfectly!

Another question if I may.

If I had 3 or 4 choices in the drop-down and I wanted to count how many of one or two (example the "F" & "R") and exclude specific others ("C" & "D") from the total how to modify the formula so that I could input which to include and which to exclude?

 

[GTT]

If you have the LET function, a slight variation to consider might also be

=LET(r,E4:E100,s,TEXTJOIN(",",1,FILTER(r,SUBTOTAL(3,OFFSET(E4,ROW(r)-ROW(E4),,1)))),LEN(s)-LEN(SUBSTITUTE(s,",",""))+1)

This works perfectly as well. Thank you Peter!

I did save my version and platform originally, and just checked and it is displaying; 365 & Windows.

 

[Peter_SSs]

This works perfectly as well. Thank you Peter!

You're welcome. Thanks for the follow-up.

I did save my version and platform originally, and just checked and it is displaying; 365 & Windows.

I'm assuming that you are referring to the blue line in my "signature block" which appears in every post, so it wasn't directed specifically at you but as a prompt for all users. You will notice that jasonb75 has something similar too.

 

[GTT]

You're welcome. Thanks for the follow-up.



I'm assuming that you are referring to the blue line in my "signature block" which appears in every post, so it wasn't directed specifically at you but as a prompt for all users. You will notice that jasonb75 has something similar too.

Yes, it was from the signature actually.

Another formula question.

If I had 3 or 4 choices in the drop-down and I wanted to count how many of one or two (example the "F" & "R") and exclude specific others ("C" & "D") from the total how to modify the formula so that I could input which to include and which to exclude?

Thank you,

 

[Peter_SSs]

If I have understood correctly, I can't see an easy way to do that without vba. Would vba be acceptable?

Also, if a cell contained "X,CFS,A,B" and you were checking for X and A, would that cell count as 2 or just 1?