# Sum of unique products

#### John117

##### Active Member
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

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

##### MrExcel MVP
a formula of the form:

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

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

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

##### Active Member

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

Replies
26
Views
827
Replies
1
Views
74
Replies
1
Views
88
Replies
1
Views
409
Replies
31
Views
601

1,141,592
Messages
5,707,287
Members
421,500
Latest member
Alex2302

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back