Highlight exact match in column and count
 

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.

Highlight exact match in column and count
 

'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.

Highlight exact match in column and count
 

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.