Create formula

  • Thread starter Thread starter Leo
  • Start date Start date
L

Leo

Formula :
=IF(B3=0,0,INDEX(data1,B3+1,MATCH($B$2,data2,0)))+IF(C3=0,0,INDEX(data1,C3+1,MATCH($C$2,data2,0)))+
.......+IF(N3=0,0,INDEX(data1,N3+1,MATCH($N$2,data2,0)))
where data1 and data2 areas from aux table.

Works fine ... , but very long ..

Does it possible to create shorter formula ( may be array) without
"+"'s
and based on range B3:N3 ?

Thanks for help
 
See if this could work:
=SUMPRODUCT(--(B3:N3<>0),INDEX(data1,B3:N3+1,MATCH($B$2,data2,0)))

Ola Sandstrom
 
Thanks for reply , but unfortunately it doesn't work .
I think the problem is here : MATCH($B$2,data2,0)

it must scroll from $B$2 to $N$2 ,but it wont's work under MATCH ...
Regards, Leo
 
Back
Top