• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count Unique Values

K

Kenshin

Member
Good day ninjas i have difficult situation about count unique values on 2 way lookup table, im attach my case workbook and the desired results......

thanks
 

Attachments

  • example.xlsx
    10.7 KB · Views: 14
Hi,

1] Change your cell A2:A18 from text value into numeric value.

2] Count non blank value, in C23 array formula copy down :

=MAX(COUNTIF(OFFSET(OFFSET(B$1,MATCH(A23,$A$1:$A$18,0)-2,),ROW(INDIRECT("1:"&COUNTIF(A$2:A$18,A23))),,,6),">0"))

p.s. Array formula must confirm enter in pressing CTRL+SHIFT+ENTER 3 keystrokes together.

3] Or, this non- array formula instead :

=AGGREGATE(14,,COUNTIF(OFFSET(OFFSET(B$1,MATCH(A23,$A$1:$A$18,0)-2,),ROW(INDIRECT("1:"&COUNTIF(A$2:A$18,A23))),,,6),">0"),1)

Regards
 

Attachments

  • CountNonBlankValue.xlsx
    11.8 KB · Views: 14
Last edited:
You must log in or register to reply here.
Back
Top