Excel Quick Tips: Count Unique Values

I’ve got an extremely short but valuable Excel tip for you today: how to count the number of unique values in a range. Sometimes you need to count values in a row or column but not if they repeat. To count only the unique numbers or words in Excel, use the following formulas depending on:

If there are no blank cells:

=SUMPRODUCT(1/COUNTIF(Range, Range))

With or without blank cells:

=SUMPRODUCT((Range<>"")/COUNTIF(Range,Range&""))

Replace Range with A1:A7 for example. 

COUNTIF is probably the function you’re most used to that will count based on a given condition. SUMPRODUCT returns the sum of the product in the range.  SUMPRODUCT functions as an array formula, you just don't have to enter it as such. 

To see how this formula works step by step, click on the cell that contains the formula, then go to Formula tab, and click Evaluate Formula and you can cycle through each step in the calculation.

For more, see the index of Excel tips page.

How to find the most common name in an Excel spreadsheet?

How do you find the most common name or string in a range in an Excel spreadsheet? The following array formula will return the most frequently used entry in a range, where Rng is the range containing the data:
 
=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng,Rng),0))

How do I count by increments in Microsoft Excel 2007?

You may be wondering how to make a list in excel count from one specified number to another in user defined increments. Well, I recently had to use this application myself and this is the format I created. A user enters the number to start counting from in the first yellow square, the number to end at in the next square, and finally, in what increments they would like to count up at. Here is the driving formula:

=IF(I2="","",IF(I2+1*$F$1<=$D$1,I2+1*$F$1,"")) and =B1 in cell I2.

Pictured below are two examples.