Hi Ken,
It appears we missed an important sign post. The interaction between two
things made me to post a wrong formula:
[1] The Top N issues;
[2] Eagerness to avoid the ROW(INDIRECT(...) idiom because the volatility
this causes.
Sum (or average, etc.) N largest/smallest values are similar to the Top N
questions. When it's not expicitly stated that the values to be processed
must be *exactly N* then I feel we should at least provide solutions for a
self-adjusting N for Nth value might have multiple instances. An OP at
MrExcel explicitly asked me for the values associated with all
instances/occurrences of the Nth value. A few weeks later I saw Harlan also
explicitly mention this to an OP at worksheet.functions. Anyway, I no
longer take such questions implicitly as 'exactly N'. The same with
questions of "how can I retrieve the associated with the Min/Max value?".
I feel that "Sum largest/Smallest N" should be based on a self-adjusting
N...
=SUMIF(Range,">"&LARGE(Range,N))+COUNTIF(Range,LARGE(Range,N))*LARGE(Range,N
)
However, Chimi's question (the OP in this thread) involves a question of
summing "exactly N" values. My failed attempt to effect the required sum
without the ROW(INDIRECT(...) idiom stranded (as Leo's sample shows), how
ironic -- considering [1], on the multiple occurrences of the Nth value [
the sign post we missed
]. The lesson is that we can't avoid the
ROW(INDIRECT(...) idiom for computations involving questions of exactly N
(where N is variablized).
The incident enabled me to correct an earlier mistake of mine though
(
http://makeashorterlink.com/?K1C5222F6)
Aladin
1,{1,2,3,4,5,6,7,8,9,10}))