Forum Discussion
counting the number of items provided that counting duplicated items one time only
I have a range of items names and I want to count the number of items provided that counting duplicated items one time only. The range or number of items is variable dynamically. How can do this? see...
Try this formula: =COUNT(UNIQUE(C6:C12))
- but the data could exceed C12 and I do not know where it ends every time
=COUNT(UNIQUE(C6:INDEX(C6:C100000,COUNTA(C6:C100000))))
- HansVogelaar
I applied this function but it returns zero value, (formula refers to empty cells). I have values in these cells resulting from the filtering process.
That formula was written to work for the example file you provided. I thought it was obvious that the C12 reference was not a permanent part of the formula; in fact, the whole range was not a permanent part of the formula. What it was illustrating was how to use UNIQUE nested in COUNT.
If you have your data in an Excel table, and the formula applies to a column in the table, it will automatically adjust to how ever many rows there are. See the attached.
