# Counting Distinct Values With Filters [closed]

Hello.

I have found the following array formula that counts how many distinct values appear on a range of cells.

{=SUMPRODUCT((Data<>"")/(COUNTIF(Data;Data)+(Data="")))}

where DATA is the range of cells.

My questions are:

1) This formula is static in relation to the filters: if one applies a filter to the range of cells, the outcome of the formula does not change. Is there any formula that would adjust itself automatically according to the filter results shown, like SUBTOTAL does?

2) What is the rationale behind this formula? How is it built? I understand what it does but I'd like to understand how it does it. In particular the COUNTIF and DATA="" part.

Thank You.

I would prefer "distinct" or "different" instead of "unique" here. In my understanding a

uniquevalue is one occurringexactly onceanyway.Thank You. "Unique" refers to the type or kind of value not the occurrence. Yet, I suppose you are right and "distinct" may create less confusion. Question modified. Tx!