Sumproduct - number & text

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

How can you 'SUM' text values?

"Kashyap" wrote:

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

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 wrote:

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

--

Dave Peterson

Then what way can I get the corresponding text?

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

Regards,
Fred.

"Kashyap" wrote in message
...
Then what way can I get the corresponding text?

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.

See http://www.contextures.com/xlFunctio...ml#IndexMatch4 for a solution

"Kashyap" wrote:

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.

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.

"Kashyap" wrote:

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

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