Find out the number of unique products in the list

Assuming you have a list of product names in a column, say one hundred rows. You want to find out how many unique items are there in the list. To do that you can use the following formula:

=SUMPRODUCT((A1:A100<>"")/(COUNTIF(A1:A100,A1:A100)+(A1:A100="")))

This is the simplest formula to use. Another you could use is as follows:

=SUM(IF(FREQUENCY(IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""),IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""))>0,1))

The second formula is longer but will return the results faster. According to one web site, it is 4-5 times faster. If this is a simple worksheet, you may not feel the difference. However, if yoiu have a complicated worksheet with multiple formulas, the second one may prove to be a time saver if it really calculates 4-5 times faster.