Sumproduct - number & text
Try this array formula** :
=INDEX(Sheet2!$D$2:$D$100,MATCH(1,(Sheet2!$C$2:$C$ 100=$C$2)*(Sheet2!$B$2:$B$100=R4),0))
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
--
Biff
Microsoft Excel MVP
"Kashyap" wrote in message
...
Hi, I have number in colB & text in Col C,D
I was trying sumproduct to go the data from colD looking at Col B & C
something like below.. But result is #VALUE!
=SUMPRODUCT((Sheet2!$C$2:$C$100=$C$2)*(Sheet2!$B$2 :$B$100=R4)*(Sheet2!$D$2:$D$100))
Can someone help me on this pls...
|