count unique values across multiple columns

[td3201]

I have a worksheet with formulas in rows C, F, J, M, Q, and T. These formulas are lookups to another sheet so the result may look like this:
[TABLE="width: 221"]
<tbody>[TR]
[TD="class: xl70, width: 221"]TT BRAVO QUICKY[/TD]
[/TR]
</tbody>[/TABLE]

The actual formula looks like this if anyone cares:

=IF(ROW($A1)>COUNTIFS('Offensive Plays'!$F$2:$F$73,$H$26,'Offensive Plays'!$AF$2:$AF$73,"RT",'Offensive Plays'!$K$2:$K$73,"Yes"),"",INDEX('Offensive Plays'!C$2:C$73,MATCH(LARGE(INDEX(('Offensive Plays'!$F$2:$F$73=$H$26)*('Offensive Plays'!$K$2:$K$73="Yes")*('Offensive Plays'!$AF$2:$AF$73="RT")*(COUNTIF('Offensive Plays'!$C$2:$C$73,">="&'Offensive Plays'!$C$2:$C$73)),0),ROW($A1)),INDEX(('Offensive Plays'!$F$2:$F$73=$H$26)*(COUNTIF('Offensive Plays'!$C$2:$C$73,">="&'Offensive Plays'!$C$2:$C$73)),0),0)))

The point is, just doing a regular countif, isn't going to work as the cells contain formulas.

I want to count all unique values in columns C, F, J, M, Q, and T. So if the value exists in C4 and T20, only count it once.
Thanks!

 

[Yongle]

Here is some VBA that should work
Assumes first column in string can be used to determine last row
Count starts in row 2
Run from sheet containing the values

Sub UniqueValues()
    Const col = "C,F,J,M,Q,T"
    Dim arr, rng As Range, cel As Range, rCount As Long, a As Long
    Dim coll As New Collection
    
    arr = Split(col, ",")
    rCount = Cells(Rows.Count, arr(0)).End(xlUp).Row - 1
    Set rng = Cells(2, arr(0)).Resize(rCount)
    For a = 1 To UBound(arr)
        Set rng = Union(rng, Cells(2, arr(a)).Resize(rCount))
    Next
    For Each cel In rng
        On Error Resume Next
        If cel <> "" Then coll.Add "X" & cel.Value, "X" & cel.Value
    Next

    MsgBox "Unique values count = " & coll.Count

End Sub

 

[td3201]

Wow, this was it, thank you. One minor issue is it appears to not be counting one somehow. I think it has to do with how it is determining rng. I have more rows in column J than C so I wonder if it's stopping short somehow.

 

[Aladin Akyurek]

Add the following code to your workbook, using Alt + F11:

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

1. Define List using

Formulas | Name Manager (or ​

Insert | Name | Define) as referring to:

=arrayunion('Offensive Plays'!$C$2:$C$73,

'Offensive Plays'!$F$2:$F$73,
'Offensive Plays'!$J$2:$J$73,

'Offensive Plays'!$M$2:$M$73,

'Offensive Plays'!$Q$2:$Q$73,

'Offensive Plays'!$T$2:$T$73)
​

2. Define Ivec as referring to:

=ROW(INDIRECT("1:"&COLUMNS(List)))

3. Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(List=""),MATCH("~"&List,List&"",0)),Ivec),1))

 

[Yongle]

One minor issue is it appears to not be counting one somehow. I think it has to do with how it is determining rng. I have more rows in column J than C so I wonder if it's stopping short somehow

One of my assumptions was "first column in string can be used to determine last row"
Here is one way of getting the column wiith the maximum number of rows to determine things:

Add the first 2 lines and replace the 3rd

Dim c As Long
    c = Rows.Count 
    rCount = WorksheetFunction.Max(Cells(c, "C").End(xlUp).Row, Cells(c, "F").End(xlUp).Row, Cells(c, "J").End(xlUp).Row, Cells(c, "M").End(xlUp).Row, Cells(c, "Q").End(xlUp).Row, Cells(c, "T").End(xlUp).Row) - 1

 

[kvsrinivasamurthy]

Try this. Enter values as below.
[TABLE="class: grid, width: 378"]
<tbody>[TR]
[TD]C2[/TD]
[TD]D2[/TD]
[TD]E2[/TD]
[TD]F2[/TD]
[TD]G2[/TD]
[TD]H2[/TD]
[TD]I2[/TD]
[TD]J2[/TD]
[TD]K2[/TD]
[TD]L2[/TD]
[TD]M2[/TD]
[TD]N2[/TD]
[TD]O2[/TD]
[TD]P2[/TD]
[TD]Q2[/TD]
[TD]R2[/TD]
[TD]S2[/TD]
[TD]T2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

ARRAY Formula for Range C3:T25 with conditions of column to count unique values

=SUM((IF((ROW($C$3:$C$25)>0)*($C$2:$T$2),(($C$3:$T$25<>"")*(1/COUNTIFS($C$3:$T$25,$C$3:$T$25&""))),"")))

How ARRAY formula is entered


Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.