0

I would like to count the number of unique selections from a column that contains multiple selections from a drop down list.

For example, column B3 contains,

Monday, Tuesday, Wednesday

The count function returns a value of 1 instead of 3 - is there any way to count the three days distinctly? Thank you!

Multiple selections from a drop down list was made possible using the VBA code from: https://docs.google.com/document/d/1JU7G_Tna2zPBtcG2TlarxKCTbuinNsg5LwBqzmuJYK8/edit

pundadesh
  • 1

1 Answers1

1

The simplest solution would be to use a UDF. (I'm not even sure it's possible with a standard formula.)

With a worksheet set up like this:

Worksheet Screenshot

Copy-paste the following code to a standard module:

'============================================================================================
' Module     : <any standard module>
' Version    : 0.1.1
' Part       : 1 of 1
' References : (Optional) Microsoft Scripting Runtime   [Scripting]
' Source     : https://superuser.com/a/1332369/763880
'============================================================================================
Option Explicit

Public Function CountDistintMultiSelections _
                ( _
                           count_array As Range _
                ) _
       As Long

  Dim dictSelections As Object                              '##Early Bound## As Scripting.Dictionary
  Set dictSelections = CreateObject("Scripting.Dictionary") '##Early Bound## = New Dictionary

  Dim celCell As Range
  For Each celCell In Intersect(count_array, count_array.Parent.UsedRange)
    Dim varSelections As Variant
    varSelections = Split(celCell.Value2, ", ")
    Dim varSelection As Variant
    For Each varSelection In varSelections
      If dictSelections.Exists(varSelection) Then
        dictSelections(varSelection) = dictSelections(varSelection) + 1
      Else
        dictSelections.Add varSelection, 1
      End If
    Next varSelection
  Next celCell
  CountDistintMultiSelections = dictSelections.Count

End Function


Enter the following formula in C2:

=CountDistintMultiSelections(B:B)

Explanation:

The code uses the Split() function to separate out the individual selections in each cell, and a dictionary to count the unique selections.

Notes:

To install the UDF, follow these steps:

  • Press Alt+F11
  • Select the menu item Insert → Module
  • Paste the code in the main window
robinCTS
  • 4,407