Log In
Register
Home
December 22, 2018
Offline
Hi Team,
I would like to have distinct count of the CodeFac as attached expected result.
Please let me know how to get the expected Distinct ID counts.
thank you.
Regards,
Aye
July 16, 2010
Offline
Hi Aye,
With dynamic arrays: =COUNT(UNIQUE(FILTER($B$2:$B$26,$A$2:$A$26=A34)))
Mynda
December 22, 2018
Offline
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
VIP
Trusted Members
December 7, 2016
Offline
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.myonlinetraininghu.....mic-arrays
Br,
Anders
July 16, 2010
Offline
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
December 22, 2018
Offline
Thanks, Mynda and Anders,
I have attached my excel version as required.
Regards,
Aye
VIP
Trusted Members
December 7, 2016
Offline
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
December 22, 2018
Offline
Thanks, Andres,
I will try to check with IT.
Regards,
Aye
Trusted Members
Moderators
November 1, 2018
Offline
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.
