Determine the Number of Unique Values with One Instance of Value from Adjacent Column

# Determine the Number of Unique Values with One Instance of Value from Adjacent Column

April 11, 2016

THIS IS THE ONE YEAR ANNIVERSARY OF LITIGATION SUPPORT TIP OF THE NIGHT.   TIPS HAVE BEEN POSTED TO THIS BLOG EACH NIGHT SINCE APRIL 10, 2015, ONLY WITH THE EXCEPTION OF TWO NIGHTS WHEN I WAS KEPT ALL NIGHT AT WORK.

This night's tip is an extension of the Excel formula descripted in the Tip of the Night for April 8, 2016 to get the number of unique values listed in a column.   Tonight's formula shows how many unique values in one column have at least one occurrence of a specified value in another column.     Say you have a spreadsheet which lists file numbers in one column and document types in another column.   You might want to be sure that each file has at least one contract in the file.     In the example below, we enter this formula in order to determine how many players with the Baltimore Orioles went at least on season without committing an error.

=SUM(IF(FREQUENCY(IF(J2:J1877=0,IF(B2:B1877<>"",MATCH("~"&B2:B1877,B2:B1877&"",0))),ROW(B2:B1877)-ROW(B2)+1),1))

This is an array formula, so after typing it in, press CTRL + SHIFT + ENTER.

See a discussion of this formula on this site: http://www.mrexcel.com/forum/excel-questions/421198-count-unique-text-values-based-condition-another-column.html