Now I understand!

Thanks!

"Aladin Akyurek" wrote:


Nick,

The formula combines two tracks...

1.

SUM(IF(FREQUENCY(NumRange,NumRange)0,1))

where the FREQUENCY(NumRange,NumRange) bit determines frequencies for
the distinct members of NumRange which also serve as an array of bins.
The wrapping IF maps all non-zero frequencies to 1's which the outer
SUM totals. The end result is a count of distinct numbers in NumRange.

2.

SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),,1))

which is due to Longre is described he

http://j-walk.com/ss/excel/eee/eee001.txt

It produces an array of 1's and 0's which stand for filtered and
unfiltered: 1 corresponds to a row that's visible, 0 to a row that's
invisble.

These two combined...

=SUM(IF(FREQUENCY(SUBTOTAL(3,OFFSET(A2:A20,ROW(A2:
A20)-MIN(ROW(A2:A20)),,1))*A2:A20,SUBTOTAL(3,OFFSET(A2:
A20,ROW(A2:A20)-MIN(ROW(A2:A20)),,1))*A2:A20)0,1))-(SUBTOTAL(3,A2:A20)<COUNTA(A2:A20))


allows us to consider just the numbers that are in the visible set.

Ndel40 Wrote:
I updated my spreadsheet to use Aladin's formula. It works great...
although
I'm not sure how the formula works. I always seek to understand and
I'm
interested in an explanation if you could please oblige.
[...]


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=273885