count unique values across multiple columns

td3201

New Member
Joined
Aug 17, 2016
Messages
7
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!
 

Some videos you may like

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
Joined
Mar 11, 2015
Messages
6,770
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 17, 2016
Messages
7
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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))
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,770
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 6, 2013
Messages
642
Try this. Enter values as below.
C2D2E2F2G2H2I2J2K2L2M2N2O2P2Q2R2S2T2
100100010010001001

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

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top