I have a column with many duplicates. I need to get a count of each
individual record, ignoring the dupes so that
x
x
y
z
z
z
would yield a count of three. There are many more, spanning from G2:G9075.
Thanks
Paul

=SUMPRODUCT((G2:G9075<"")/COUNTIF(G2:G9075,G2:G9075&""))
--
Gary''s Student - gsnu200815

try this

=SUM(1/COUNTIF(A2:A7,A2:A7)) ( use ctrl + shift + enter )


On Nov 24, 6:29*pm, PA wrote:
I have a column with many duplicates. *I need to get a count of each
individual record, ignoring the dupes so that
x
x
y
z
z
z
would yield a count of three. *There are many more, spanning from G2:G9075.
Thanks
Paul

Very cool, thanks.
If you have an extra moment, could you explain this. That would be a great
help as I am attempting to master the very versitile Sumproduct function, and
have made strides, but obviously still need to study further.

Thank you

"Gary''s Student" wrote:

=SUMPRODUCT((G2:G9075<"")/COUNTIF(G2:G9075,G2:G9075&""))
--
Gary''s Student - gsnu200815

That works as well, thank you

"muddan madhu" wrote:

try this

=SUM(1/COUNTIF(A2:A7,A2:A7)) ( use ctrl + shift + enter )


On Nov 24, 6:29 pm, PA wrote:
I have a column with many duplicates. I need to get a count of each
individual record, ignoring the dupes so that
x
x
y
z
z
z
would yield a count of three. There are many more, spanning from G2:G9075.
Thanks
Paul

Your formula will fail if there is a blank cell in the range


"muddan madhu" wrote:

try this

=SUM(1/COUNTIF(A2:A7,A2:A7)) ( use ctrl + shift + enter )


On Nov 24, 6:29 pm, PA wrote:
I have a column with many duplicates. I need to get a count of each
individual record, ignoring the dupes so that
x
x
y
z
z
z
would yield a count of three. There are many more, spanning from G2:G9075.
Thanks
Paul

I hadnt noticed that, since I have no blanks in my field, but it certainly
could be a problem.

"Teethless mama" wrote:

Your formula will fail if there is a blank cell in the range


"muddan madhu" wrote:

try this

=SUM(1/COUNTIF(A2:A7,A2:A7)) ( use ctrl + shift + enter )


On Nov 24, 6:29 pm, PA wrote:
I have a column with many duplicates. I need to get a count of each
individual record, ignoring the dupes so that
x
x
y
z
z
z
would yield a count of three. There are many more, spanning from G2:G9075.
Thanks
Paul

Hi,

If the range contained only numbers you could use this

=SUMPRODUCT(--(FREQUENCY(B1:B10,B1:B10)0))

Regarding how the two previous formulas work sustituting a smaller range:

COUNTIF(B1:B10,B1:B10&"") This portion of the formula counts how many time
each item in the range B1:B10 appears in the range B1:B10. It might return
something like this
{4;5;4;5;4;5;1;5;4;5}
If somethng is repeated 5 times there are 5 fives listed. If there are 5
fives then 1 unique items was found, if there were 10 fives that would mean 2
unique items had been found. If a 1 appears it means an item was only found
once. If there are 10 1's there were ten uniques items that appeared only
once.

The B1:B10<"" portion is just checking to see if the cells are not blank,
but it return something like
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRU E}

Now when you divide (or add, subtract, multiply) a number into a TRUE Excel
converts the TRUE to 1, similarly a FALSE becomes 0. So in effect, think of
this as

{1;1;1;1;1;1;0;1;1;1}

Together this is

{1;1;1;1;1;1;0;1;1;1}/{4;5;4;5;4;5;1;5;4;5}

Which returns

{0.25;0.2;0.25;0.2;0.25;0.2;0;0.2;0.25;0.2}

Notice if an item appeared 5 times it carries a weight of .2, if it appeared
twice it has a weight of .5. If you add 5 .2's you get 1, if you add 2
..5's you get 1.
Sum this up and you have the number of unique items.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"PA" wrote:

I have a column with many duplicates. I need to get a count of each
individual record, ignoring the dupes so that
x
x
y
z
z
z
would yield a count of three. There are many more, spanning from G2:G9075.
Thanks
Paul