Count of unique MAC address entries from a dynamic range

[Sparky]

I have a dynamic range of data in column D (starting at D3), it can extend to 40,000 rows.
It consists of MAC addresses in this format 94:FB:29:06:1C:E4

How do I count the unique MAC address entries to appear in cell D2?

Thanks in advance

 

[sandy666]

You want to sum the number of unique MACs?

use XL2BB to post representative example and expected result

 

[alz]

Hi Try This!

Use this if there is no blank in range D3:D40000,
=SUMPRODUCT(1/COUNTIFS(D3:D40000,D3:D40000))

Use this if there is blank in range D3:D40000, ctrl+shift+enter
{=SUMPRODUCT(IF(D3:D40000<>"",1/COUNTIFS(D3:D40000,D3:D40000)))}

 

[Fluff]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Depending on your version another option might be

=COUNTA(UNIQUE(FILTER(D3:D40000,D3:D40000<>"")))

 

[Sparky]

You want to sum the number of unique MACs?

use XL2BB to post representative example and expected result

sandy666

I will use for future posts.
Thank you.

 

[Sparky]

Hi Try This!

Use this if there is no blank in range D3:D40000,
=SUMPRODUCT(1/COUNTIFS(D3:D40000,D3:D40000))

Use this if there is blank in range D3:D40000, ctrl+shift+enter
{=SUMPRODUCT(IF(D3:D40000<>"",1/COUNTIFS(D3:D40000,D3:D40000)))}

alz

Thank you
This gives the desired outcome.