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