How to count unique combined column values

Popular categories

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Get Digital Help > Excel > How to count unique combined column values

← Previous post -

Filed in Excel on Feb.03, 2009. Email This article to a Friend

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".