Formulas to find duplicates to include filtering

[brummingham]

I have the following formula to count how many duplicates are in my spreadsheet:

=SUMPRODUCT((M7:M98<>"")/COUNTIF(M7:M98,M7:M98&"")+0)

This works fine and identifies the number of duplicates. However I need it configured to work for when data is filtered. When I filter data the value remains the same. Can someone help me out please

[XLent]

Personally, I'd suggest doing the following...

In a blank column - e.g. Z for purposes of illustration:

Please Login or Register  to view this content.

your unique count then becomes:

Please Login or Register  to view this content.

one key advantage of the use of SUBTOTAL in Col Z is that it negates the use of the same in the SUMPRODUCT
SUBTOTAL embedded within a SUMPRODUCT calc also requires use of OFFSET thus making an already 'expensive' calc Volatile.

note: if you want to exclude physically hidden rows, (e.g. rows hide), as opposed to just auto-filtered rows - change 3 to 103 in the SUBTOTAL.

[brummingham]

I did this . I created a column in N and it returned the value of 1 in each cell. I the. Made the changes to the main formula but it returned the #value error

[XLent]

Hi, if you can, post a sample file illustrating the issue -- to do this click GoAdvanced (to reply), then click Manage Attachments and follow on screen prompts
(unfortunately the paperclip icon doesn't work here)

I've taken the liberty of attaching a working version of earlier proposal - in case it helps isolate the issue, your end?
(it uses a rand to calc so if you press F9 you should see result changes to reflect latest data - to help prove it's working, conceptually)

[brummingham]

Your formal works. However, when I filter data the number stays the same. I need the number to change based on values in the filtered data

[XLent]

"...when I filter data the number stays the same"

that would only be the case if the answer remained the same... if you have the file open, as provided, and press F9 repeatedly it will change
(not necessarily every time if the random result remains unchanged)

note: filtering rows is a volatile action so would always instigate a re-calc if running on auto calculation

I think it would be simplest / best for you to post your own sample, illustrating the problem(s).

[brummingham]

I can’t post the exact one due to data protection. I will try and upload a a dummy version

[brummingham]

As you can see by the attached it recognises 5 separate names (yeah sorry included the actually word name to) but when I filter either by age if I wanted to know how many people are at that age the figure stays at five

[XLent]

so, the attached uses the earlier suggestion; as you filter so the unique count will update drop.