If you want to determine how many unique entries are in a list. Here we have a list of repeated items but you want to know the number of unique fruits.  There are 9 kinds of fruits: Apple, Banana, Grape, Kiwi, Orange, Peach, Plum, Raisin, and Strawberry.  Unfortunately, there is no single function in Excel that will determine this number. You can use a combination of MATCH and FREQUENT functions nested with IF and SUM to produce the desired result. Bryon Smedley of Tennessee submitted a formula to myOnlineTraininghub.


  1.  Highlight the column of text and name it fruitlist.
  2. Go to an empty cell and type the following formula:
    =SUM(IF(FREQUENCY(MATCH(FruitList,FruitList,0),MATCH(FruitList, FruitList,0))>0,1))
  3. Hit Enter
  1. For an easier formula, highlight the column of text and name it fruitlist.
  2. Got to an empty cell and type the following formula:
    =SUM(1/COUNTIF(FruitList,FruitList))
  3. DO NOT hit ENTER but CRTL+SHIFT+ENTER since it will be an array called fruit list. Excel knows it is a One Table Array. It will also place { } around the formula.


					
Advertisements