Sumproduct - number & text

[Kashyap]

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...

 

[Sheeloo]

How can you 'SUM' text values?

 

[Dave Peterson]

If you use that * operation, then if you have any non-numeric entries in
D2100, then you'll get that error.

But if you change your syntax:

=SUMPRODUCT(--(Sheet2!$C$2:$C$100=$C$2),
--(Sheet2!$B$2:$B$100=R4),
--(Sheet2!$D$2:$D$100))

Text in D2100 will be treated as 0--just like =sum().

 

[Kashyap]

Then what way can I get the corresponding text?

 

[Fred Smith]

You'll have to specify how you want to sum the text. Give an example and
show the results you want.

Regards,
Fred.

 

[Kashyap]

I just want to lookup 2 columns and get the result from the 3rd column. But
here 2 columns are text and number in one column.

 

[Sheeloo]

See http://www.contextures.com/xlFunctions03.html#IndexMatch4 for a solution

 

[PJ]

Hi Kayshap

Try this:

With the criteria you want to look up in E1 and F1

=INDEX($D$2:$D$100,MATCH($E$1&$F$1,$B$2:$B$100&$C$2:$C$100,0))

this is an array formula entered by pressing Ctrl. Shift and Enter together.

This should return the value from the row in column D where the cells in
columns B and C have the values specified in E1 and F1.

 

[T. Valko]

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.