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.
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,1,16,12,9,6,6,9,12,11,4}>={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24}
{TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE, TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE, FALSE,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.
count-unique-combined-column-values.xls
(Excel 97-2003 Workbook *.xls)

SUM(number1,[number2],)
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".

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Count unique distinct values in three columns combined in excel
  2. Count unique values and unique distinct values in two ranges combined
  3. Count unique values and unique distinct values in three ranges combined in excel
  4. Count duplicate distinct values in a column in excel
  5. Count unique distinct values in a column in excel
  6. Count unique and unique distinct values in a multicolumn range in excel
  7. Count unique values in two lists combined in excel
  8. Count unique distinct values in two columns in excel
  9. Count unique distinct values in two columns with date criteria in excel