how can you count the amount of filter options?

Question

I want the total filter options without having to count each individual one.

Thank you

Answer 1

One question when it is counting the blanks is it only counting it once? So everytime I know there are blank entries that I do not want to count I put a ( -1 ) at the end?

In this formula from my answer above

=COUNTA(UNIQUE(B:B))-2

the COUNTA function will count all cells with data in the specified range or in this case the array created by the UNIQUE function. The UNIQUE function here creates an array that eliminates all of the duplicates and keeps only one of each entry comparable to the Filter dropdown.

However, since the formula refers to the entire column the unique array also includes the header and any blank cells either in the middle or below the data. So, the array created by the UNIQUE function will have one unique entry of every piece of data in the column including the header and one 0 representing all of the blank cells.

The -2 at the end is to account for the header and that one entry of 0 that represents all of the blank cells so that the number left is the actual number of unique items in the column. You shouldn't ever have to change that as long as the formula refers to the entire column, B:B.

Answer 2

If you are replacing the B:B reference with an actual range like B2:B1250 you wouldn't need to subtract anything unless there are blanks in the range. Then you would just subtract -1 because the UNIQUE formula will only include one entry representing all of the blank cells no matter how many there are.

=COUNTA(UNIQUE(B2:B1250))

or there if there are blanks in the range

=COUNTA(UNIQUE(B2:B1250))-1