Revision history [back]

Counting Unique Values With Filters

Hello.

I have found the following array formula that counts how many unique 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: 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.

Counting Unique Values With Filters

Hello.

I have found the following array formula that counts how many unique 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: 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.

Counting Unique Values With Filters

Hello.

I have found the following array formula that counts how many unique 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: 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.

Counting Unique Distinct Values With Filters

Hello.

I have found the following array formula that counts how many unique 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: 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.