I am using this CF to find my duplicates =COUNTIF(A1:A$10000,A1)>1. The next 
part I need is to count the number of duplicates (gray shaded & 
strikethough). I have tried a few VBA but I read they don't work with formula 
based CF's.
I could also sort the gray shaded then count them with a helper column but 
ideally would be have one cell at the bottom of my 8000 rows that total my 
duplicates.

'to display the distinct items in the range
=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))

'duplicate items in the range..
=COUNTA(A1:A20)-SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))


-- 
Jacob (MVP - Excel)


"mgbcab" wrote:

> I am using this CF to find my duplicates =COUNTIF(A1:A$10000,A1)>1. The next 
> part I need is to count the number of duplicates (gray shaded & 
> strikethough). I have tried a few VBA but I read they don't work with formula 
> based CF's.
> I could also sort the gray shaded then count them with a helper column but 
> ideally would be have one cell at the bottom of my 8000 rows that total my 
> duplicates.
> 

Thanks, your 2nd formula did exactly what I needed.

"Jacob Skaria" wrote:

> 'to display the distinct items in the range
> =SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))
> 
> 'duplicate items in the range..
> =COUNTA(A1:A20)-SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))
> 
> 
> -- 
> Jacob (MVP - Excel)
> 
> 
> "mgbcab" wrote:
> 
> > I am using this CF to find my duplicates =COUNTIF(A1:A$10000,A1)>1. The next 
> > part I need is to count the number of duplicates (gray shaded & 
> > strikethough). I have tried a few VBA but I read they don't work with formula 
> > based CF's.
> > I could also sort the gray shaded then count them with a helper column but 
> > ideally would be have one cell at the bottom of my 8000 rows that total my 
> > duplicates.
> >