COUNTIFS to remove duplicates

[RookieHelp!]

Hello,

So i have a list of account names in column D and I would like to remove the duplicates.

So far I have an array formula that works:

Please Login or Register  to view this content.

(Although if there is a way of having the whole of column D that would be better as D:D doesn't seem to work).

I would now like to add in another 'if' so that it only counts the duplicates from column C if it has a specific persons name that owns that active account.

I appreciate any help!

Thank you!

[benishiryo]

hi there. just for your info, you could have avoided the array formula by using:
=SUMPRODUCT(('Active Accounts'!D2:D2000<>"")/COUNTIF('Active Accounts'!D2:D2000,'Active Accounts'!D2:D2000&""))

but for your requirement of a criteria, you would have to use an array formula again:

Please Login or Register  to view this content.

[RookieHelp!]

Thank you very much, that seems to have worked.

However if there is a gap or blank cell in column D then it gives up an error. Is there any way around this?

Thank you