Forum Discussion
COUNT unique values only in FILTERED dataset
Hi!
I have a dataset that in principle looks like this (I have attached the file).
I have to divide the value in one cell (column B) by the number of UNIQUE values in one column (column C). But, not the entire column, just what corresponds to one of the Blocks (column A)
So first I tried
=B2/COUNTA(UNIQUE($C$2:$C$31))
Which is the value calculated in column D, but that corresponds to the value in B divided by the total number of unique values in column C. So it is not what I'm looking for.
In column D, I calculated manually the value that I am interested in. I did it by manually adjusting the range of each row according to the Block number in column A. Which corresponds respectively to the formula:
=B2/COUNTA(UNIQUE($C$2:$C$16))
=B2/COUNTA(UNIQUE($C$17:$C$21))
=B2/COUNTA(UNIQUE($C$22:$C$31))
The problem is that my real data set has tens of thousands of rows and it would take too long to do this manually. Can someone think of a better way to do it?
- Perhaps you can use the filter function:
=B2/COUNTA(UNIQUE(Filter($C$2:$C$31, $A$2:$A$31=A2)))
