LinkBack URL
About LinkBacks
Hi,
I am trying to count how may unique values there are in C based on based on B and E.
Currently I have. Result should be 2 but formula returns 3. Sample enclosed.
Formula:
Please Login or Register to view this content.
Thanks
Thomas
Hi,
I am trying to count how may unique values there are in C based on based on B and E.
Currently I have. Result should be 2 but formula returns 3. Sample enclosed.
Formula:Please Login or Register to view this content.
Thanks
Thomas
Last edited by thomasuponor; 03-26-2021 at 08:27 AM.
your values in C are different (one has trailing space the other does not)
I would probably use:
=SUM(SIGN(FREQUENCY(IF($E$2:$E$14=K$1;IF(TEXT($B$2:$B$14;"yymm")=TEXT($I2;"yymm");MATCH(TRIM($C$2:$C$14);TRIM($C$2:$C$14);0)));ROW($1:$5988))))
confirmed with CTRL + SHIFT + ENTER
the embedded IF approach will limit the 'expense' of the overall calculation -- more relevant if (in real-life) you're using in large volume, or with large ranges
Hi, I see the idea but unfortunately it's not working. I am attaching bigger sample.
not sure I follow, if I open the file and calculate - I get the answer of 2; what are you expecting ?
Please try at G2
=COUNT(1/FREQUENCY(IF($C$2:$C$7000=G$1;IF($A$2:$A$7000>=$E2;IF($A$2:$A$7000<EDATE($E2;1);MATCH($B$2:$B$7000;$B$2:$B$7000;))));ROW($B$1:$B$7000)))
Ctrl+Shift+Enter
If you try it on the new sample with larger data set then it's not working. If you try to H2=UFH you get 144. The result should be 72.
Yes. that's exactly what I was looking for! THX
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks