# How to calculate number of distinct entries in field across two tables using a formula?

I have two tables that contain different information, but both have a field called 'Model'. I want to know how many distinct types of model there are in both tables.

I can easily find out the number of distinct types for each table on its own using the formula below, which I found here.

``````=SUMPRODUCT((table[field]<>"")/COUNTIF(table[field],table[field]&""))
``````

However I cannot think of a way to do the same thing for two separate lists that I want to combine into a single column for use in a single equation?

Try this short User Defined Function:

``````Public Function Kount(ParamArray Rng()) As Long
Dim i As Long, r As Range, c As Collection, rr As Range

Set c = New Collection

On Error Resume Next
For i = LBound(Rng) To UBound(Rng)
Set r = Rng(i)
For Each rr In r
Next rr
Next i
On Error GoTo 0

Kount = c.Count
Set c = Nothing
End Function
``````

It will calculate the number of distinct entries across any number of disjoint ranges:

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)