Hi There,


Underneath formula with helper cell (or defined name) suits its
purposes!
=SUMPRODUCT(--(INDIRECT(ADDRESS(2;3;1;1;)&":"&ADDRESS(Maxi;3;1;1 ;))=C2))
with
Maxi=MAX(CHOOSE({1,2};IF(ISNUMBER(MATCH(REPT("z";2 55);A:A));MATCH(REPT("z";255);A:A);0);MATCH(9,9999 9999999999E+307;A:A)))

Though combining it: i.e. No helper cell! ....
=SUMPRODUCT(--(INDIRECT(ADDRESS(2;3;1;1;)&":"&ADDRESS(MAX(CHOOSE ({1,2};IF(ISNUMBER(MATCH(REPT("z";255);A:A));MATCH (REPT("z";255);A:A);0);MATCH(9,99999999999999E+307 ;A:A)));3;1;1;))=C2))

Excel tells me there is an error?!
This puzzles me.
Is it maybe because you cannot nest more than 7 functions ... like you
cannot nest more than 7 IF's?

Any insight, highly appreciated, Sige

<Is it maybe because you cannot nest more than 7 functions ... like you
cannot nest more than 7 IF's?

The 7 nested IFs are often mentioned, but in fact it is simply 7 nested
levels of functions. IF or otherwise.

--
Kind regards,

Niek Otten

"Sige" wrote in message
oups.com...
Hi There,


Underneath formula with helper cell (or defined name) suits its
purposes!
=SUMPRODUCT(--(INDIRECT(ADDRESS(2;3;1;1;)&":"&ADDRESS(Maxi;3;1;1 ;))=C2))
with
Maxi=MAX(CHOOSE({1,2};IF(ISNUMBER(MATCH(REPT("z";2 55);A:A));MATCH(REPT("z";255);A:A);0);MATCH(9,9999 9999999999E+307;A:A)))

Though combining it: i.e. No helper cell! ....
=SUMPRODUCT(--(INDIRECT(ADDRESS(2;3;1;1;)&":"&ADDRESS(MAX(CHOOSE ({1,2};IF(ISNUMBER(MATCH(REPT("z";255);A:A));MATCH (REPT("z";255);A:A);0);MATCH(9,99999999999999E+307 ;A:A)));3;1;1;))=C2))

Excel tells me there is an error?!
This puzzles me.
Is it maybe because you cannot nest more than 7 functions ... like you
cannot nest more than 7 IF's?

Any insight, highly appreciated, Sige

Hi Niek,

Sounds logic!

Thanks Sige