Sum of unique products

John117

Active Member
Joined
Sep 29, 2004
Messages
371
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

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Active Member
Joined
Sep 29, 2004
Messages
371
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
 

Forum statistics

Threads
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?

Disable AdBlock

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
Top