count unique transactions

[erniepoe]

hi all,

I want to count the unique numbers in column B given each user ID number in column I. For the first User ID (22), I have the following CSE formula.

any idea what i am doing wrong? Thanks!

If(i2=22,sum(1/countif(B2:B5000,B2:b5000)))

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(IF(FREQUENCY(IF($I$2:$I$5000=22,IF(LEN($B$2:$B$5000)>0,MATCH($B$2:$B$5000,$B$2:$B$5000,0))),ROW($B$2:$B$5000)-ROW($B$2)+1)>0,1))

Hope this helps!