# count unique values across multiple columns

#### td3201

##### New Member
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:
 TT BRAVO QUICKY

<tbody>
</tbody>

The actual formula looks like this if anyone cares:
Code:
``=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!

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### Yongle

##### Well-known Member
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

Code:
``````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

##### New Member
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.

##### MrExcel MVP

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' 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))

Last edited:

#### Yongle

##### Well-known Member
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
Code:
``````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``````

Last edited:

#### kvsrinivasamurthy

##### Well-known Member
Try this. Enter values as below.
 C2 D2 E2 F2 G2 H2 I2 J2 K2 L2 M2 N2 O2 P2 Q2 R2 S2 T2 1 0 0 1 0 0 0 1 0 0 1 0 0 0 1 0 0 1

<tbody>
</tbody>

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

Code:
``=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.

Last edited:

Replies
3
Views
54
Replies
12
Views
143
Replies
1
Views
107
Replies
2
Views
44
Replies
5
Views
73

1,109,341
Messages
5,528,146
Members
409,803
Latest member
Jeff abby

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...