Snasui Excel Forum

Hi Dear
I need your help to extract product code from the range and count value below them.
I also attached a sample

Thanks

C2
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($2:$15)/ISTEXT($A$2:$A$15),ROW(1:1))),"")


D2
=IFERROR(SUM(INDEX(--(LOOKUP(ROW($2:$15)-ROW($1:$1),MATCH($C$2:$C$9,$A$2:$A$15,0))=MATCH(C2,$A$2:$A$15,0)),0))-1,"")

Norkaz

Could you explain your second formula?

Hi Guy


From D2 =IFERROR(SUM(INDEX(--(LOOKUP(ROW($2:$15)-ROW($1:$1),MATCH($C$2:$C$9,$A$2:$A$15,0))=MATCH(C2,$A$2:$A$15,0)),0))-1,"")

a) Formula =MATCH($C$2:$C$8,$A$2:$A$15,0)
Result (F9) ={1;5;9;#N/A;#N/A;#N/A;#N/A}
======================

b) Formula =LOOKUP(ROW($2:$15)-ROW($1:$1),MATCH($C$2:$C$9,$A$2:$A$15,0))


Result (F9) ={1;1;1;1;5;5;5;5;9;9;9;9;9;9}

======================

c) Formula =MATCH(C2,$A$2:$A$15,0)
Result (F9) =1
======================

d) b=c Formula =LOOKUP(ROW($2:$15)-ROW($1:$1),MATCH($C$2:$C$9,$A$2:$A$15,0))=MATCH(C2,$A$2:$A$15,0)

Result (F9) ={TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

======================

e) Formula =--(LOOKUP(ROW($2:$15)-ROW($1:$1),MATCH($C$2:$C$9,$A$2:$A$15,0))=MATCH(C2,$A$2:$A$15,0))

Result (F9) ={1;1;1;1;0;0;0;0;0;0;0;0;0;0}

======================


f) Formula =INDEX(--(LOOKUP(ROW($2:$15)-ROW($1:$1),MATCH($C$2:$C$9,$A$2:$A$15,0))=MATCH(C2,$A$2:$A$15,0)),0)

Result (F9) ={1;1;1;1;0;0;0;0;0;0;0;0;0;0}

======================

g) Then , SUM-1

h) Then, IFERROR


Hope this helps.

Norkaz

Thank you