CODEWITHSUNDEEP
excel
e-malito
e-malito

Reputation: 938

Number of unique values in an excel table column while a filter is active

In an Excel table, one can filter the data of each column by clicking on the arrow next to the header. When clicking on this arrow, Excel displays a list of the unique values present in the column with a checkbox next to each values. This list changes when filters are applied on other columns. The number of unique values within a column if thus different depending on the filters that are applied.

I want to dynamically compute the number of unique values in each column and store that in a row above the table headers. These values should update when different filters are applied on the table, just like Excel updates the list when one clicks on the headers' arrow.

Where I've got so far:

Upvotes: 1

Views: 672

Answers (1)

XOR LX
XOR LX

Reputation: 7762

Assuming that:

1) You are using table names in formulas

2) Your table is named "Tab"

3) The first column in your table is named "Column1"

4) An empty cell is equally to be considered a unique value

then place this array formula** in a cell somewhere within the same column as that containing "Column1", preferably such that it is external to the table itself:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(INDEX(Tab,1,COLUMNS($A:A)),ROW(Tab[Column1])-MIN(ROW(Tab[Column1])),)),MATCH(INDEX(Tab,,COLUMNS($A:A)),INDEX(Tab,,COLUMNS($A:A)),0)),ROW(Tab[Column1])-MIN(ROW(Tab[Column1]))+1),1))

Copy to the right as required.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Upvotes: 1

Related Questions