Sumproduct function and condition...

[umss]

Hello guys,

I can not pick data from Sheet2 to sheet1 through sumproduct function as you can see in attached work book.

May be combination of index and match function could work?

Thank you in advance for all your help!

[NBVC]

Try:

=INDEX(Sheet2!$B$2:$I$11,MATCH($A2,Sheet2!$A$2:$A$11),MATCH(B$1,Sheet2!$B$1:$I$1))

[umss]

Wow!!! I appreciate it...

But we do have any option to use SUMPRODUCT function?

Thanks

[NBVC]

That would be:

=SUMPRODUCT((Sheet2!$A$2:$A$11=$A2)*(Sheet2!$B$1:$I$1=B$1),Sheet2!$B$2:$I$11)

but is not the more efficient solution.

or a more efficient alternative...

=SUMIF(Sheet2!$B$1:$I$1,B$1,INDEX(Sheet2!$B$2:$I$11,MATCH($A2,Sheet2!$A$2:$A$11),0))

[umss]

It is great.

I am learning new techniques

Thanks a lot