Sum of unique products

[John117]

Hello

Staring with row 8 in column C I have list of product description. This list varies weekly i.e. one wk could be C8:C278 next wk C8:C1345 etc. there is lot of duplicate descriptions within this list. I am seeking a formula (Not VBA code) to show how many unique products I have in Col C – starting with row 8. Col C is formatted as a Text.
Can you help me with this one??

Thank you
John

 

[PaddyD]

a formula of the form:

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

...array entered with control + shift + enter.

see this thread:

http://www.mrexcel.com/board2/viewtopic.php?t=73502

...for details on what the formula's doing.

& search the board for 'dynamic named range' for techniques on how to get a range reference to automatically grow / shrink with your data.

 

[John117]

Thank you Paddy

Here is the formula I have used:
{=SUMPRODUCT((C16:C500<>"")/COUNTIF(C16:C500,C16:C500&""))}

Works very well. Now I have to find a solution for the dynamic range for my data (list of product).

Thanks
John