Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

I have found a possible solution gathering ideas from some of the comments to my question here and elsewhere.

Here it goes:

1) Using the function AGGREGATE (3,5,Cell) for each single cell in the "Data" range the result would be an array of cells containing 1 (or TRUE) if Cell is shown or 0 (or FALSE) is Cell is hidden. This array can be placed aside (in a spare column) to be used as a reference.

2) Now, there's only need to add *(Cell-array=1) to the above formula, which would become:

=SUMPRODUCT((Data<>"")*(Cell-array=1)/(COUNTIF(Data;Data)+(Data="")))

where "Data" is the range from which the non-empty/distinct values are to be counted and "Cell-array" is the range of cells from which the shown cells (non-hidden) are to be counted.