Hi Aye,

With dynamic arrays: =COUNT(UNIQUE(FILTER($B$2:$B$26,$A$2:$A$26=A34)))

Mynda

Hi Mynda,

Thanks for your reply.

I tried the formula with Ctrl+Shift+Enter and I got the error as attached.

thank you.

Regards,

Aye

Hello,

What Excel version do you have? The dynamic arrays Mynda is using works currently from Excel 365 version 1907 build 11901.20176.

More about these dynamic arrays here. https://www.myonlinetraininghub.com/excel-dynamic-arrays

Br,
Anders

Hi Aye, 

Like Anders said, that formula uses dynamic arrays. You don't need to enter them with CTRL+SHIFT+ENTER

Please advise what version of Excel you have so we know what solutions will work for you.

Mynda

Thanks, Mynda and Anders,

I have attached my excel version as required.

Regards, 

Aye

Hello,

You have an old version that does not have these dynamic arrays. Your version is 1902 build 11328.20438. Ask your IT department for an update to at least version 2002 build 12527.20988.

Br,
Anders

Thanks, Andres,

I will try to check with IT.

Regards,

Aye

To work with your current version:

=COUNT(1/FREQUENCY(IF($A$2:$A$26=A34,MATCH($B$2:$B$26,$B$2:$B$26,0)),MATCH($B$2:$B$26,$B$2:$B$26,0)))

array entered.