Article updated on August 28, 2017

In a previous article Lookup Unique based on Multiple Conditions
and Automatically filter unique row records from multiple columns,
I presented a solution to filter unique row records.
In this article i am going to explain how to count the number of unique row records.

Here is the array formula:
=SUM(IF(((MATCH(A2:A25&B2:B25&C2:C25&D2:D25,A2:A25&B2:B25&C2:C25&D2:D25,0)) >=(ROW(A2:A25)-(MIN(ROW(A2:A25))-1)))=TRUE,1,0)) + Ctrl + Shift + Enter

MATCH(A2:A25&B2:B25&C2:C25&D2:D25,A2:A25&B2:B25&C2:C25&D2:D25,0)) >=(ROW(A2:A25)-(MIN(ROW(A2:A25))-1) This part of the formula filter out those values that are unique and return TRUE or FALSE.

IF(((MATCH(A2:A25&B2:B25&C2:C25&D2:D25,A2:A25&B2:B25&C2:C25&D2:D25,0)) >=(ROW(A2:A25)-(MIN(ROW(A2:A25))-1)))=TRUE,1,0)) This part converts TRUE to 1 and False to 0.

Sum() sums the 1 and 0:s.

Download excel sample file for this tutorial.

(Excel 97-2003 Workbook *.xls)

Adds all the numbers in a range of cells

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

This blog article is one out of twelve articles on the same subject "count unique and unique distinct".