Count unique values within a dynamic hierarchal range

[Jesse]

Hi there, I've read up on counting unique number of entries:

sumproduct(1/countif(A2:A100, A2:A100))


What I'm trying to do now is do this for a dynamic subset of this range. 


I'm using Excel's Table features, and it's set up like this:

[pre][code]Level1    Level2          Count of Unique Level2 for each Level1
Water    Quantity         2
Water    Quantity         2
Water    Variance         2
Water    Variance         2
Water    Variance         2
Health   Knowledge        3
Health   Knowledge        3
Health   Knowledge        3
Health   Practice         3
Health   Attitude         3
Env      ...              ...
...[/pre]
I want to count unique items in Level2

for every unique Level1[/code] entry, on each row. Any help on calculating the third column? I'll keep working on it, thanks in advance

 

[Jesse]

Found something!


http://chandoo.org/forums/topic/i-need-to-count-the-number-of-unique-values-in-a-list-given-a-variable-criteria


Which links to this file with the solution:


https://skydrive.live.com/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21119 (Link to spreadsheet example: copy and paste URL)

 

[NARAYANK991]

Hi Jesse ,


The formula you have used is the same that can be used ; only you need to change the array references according to your requirement :


=SUM(1/COUNTIF(OFFSET($B$2:$B$10,MATCH(A2,$A$2:$A$10,0)-1,,SUM(IF($A$2:$A$10=A2,1,0)),1),OFFSET($B$2:$B$10,MATCH(A2,$A$2:$A$10,0)-1,,SUM(IF($A$2:$A$10=A2,1,0)),1)))


entered as an array formula , using CTRL SHIFT ENTER.


OFFSET($B$2:$B$10,MATCH(A2,$A$2:$A$10,0)-1,,SUM(IF($A$2:$A$10=A2,1,0)),1)


is the part which creates the required array ; it references the values in column B , matches the value in A2 ("Water") with to find out the starting row , uses the SUM(IF($A$2:$A$10=A2,1,0)) function to find out how many items to include in the array.


The formula depends on all the "Water" items ( Level1 ) being together ; if they are dispersed in column A in non-contiguous cells , it will not work. The data in column B ( Level2 ) can be in non-contiguous cells.


Narayan

 

[Sajan]

Hi Jesse,

Here are a couple of additional options for counting the unique values:


=SUM(SIGN(FREQUENCY(IF(Level1=A2,MATCH(Level2,Level2,0)),IF(Level1=A2,MATCH(Level2,Level2,0)))))


or the simpler

=COUNT(MATCH(ROW($A$1:INDEX(A:A,ROWS(Level2))),IF(Level1=A2,MATCH(Level2,Level2,0)),0))


both formulas need to be entered with Ctrl + Shift + Enter


The values in Level1 and Level2 can be in any order. However, the second formula ignores any blanks in Level2.


Cheers,

Sajan.