Sumproduct array results parsed to vlookup

[Berlina]

<table width="256" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt;width:48pt" width="64" height="20">Title</td> <td class="xl70" style="width:48pt" width="64">List</td> <td class="xl70" style="width:48pt" width="64">Code</td> <td class="xl70" style="width:48pt" width="64">Rate</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Infini</td> <td>Infini</td> <td>inf-01</td> <td class="xl70">1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Infini</td> <td>Fibre</td> <td>inf-02</td> <td class="xl70">2</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Fibre</td> <td>
</td> <td>fib-01</td> <td class="xl70">10</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Copper</td> <td>
</td> <td>cop-01</td> <td class="xl70">20</td> </tr> </tbody></table>
I trying use sumproduct to: lookup ColA for items matching ColB and sum the corresponding values in ColC referenced to values in ColD.
For example the formula I am using is:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,B2:B3,0))),D2:D5) which answers 13 (Correct).

The Formula I need to use is:
=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,B2:B3,0))),C2:C5) where C2:C5 are resolved/matched/vlookup D2:D5

I tried =SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,B2:B3,0))),vlookup(C2:C5,D2:D5,1,False)) but get errors.

Help much appreciated.

 

[Tigs81]

Hi Berlina
You could try and make a 5th column for the extraction of the number using something like this, which does strings upto 9 char change the number to something else if you need it for a bigger character string.

=1*MID(C2,MATCH(TRUE,ISNUMBER(1*MID(C2,ROW($1:$9),1)),0),COUNT(1*MID(C2,ROW($1:$9),1)))

use this as an array formula - IMPORTANT - You must enter these formulas as arrays by pressing CTRL+SHIFT+ENTER.

then use your

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,B2:B3,0))),E2:E5)

Thanks
Tigs

 

[Berlina]

Hi Berlina
You could try and make a 5th column for the extraction of the number using something like this, which does strings upto 9 char change the number to something else if you need it for a bigger character string.

=1*MID(C2,MATCH(TRUE,ISNUMBER(1*MID(C2,ROW($1:$9),1)),0),COUNT(1*MID(C2,ROW($1:$9),1)))

use this as an array formula - IMPORTANT - You must enter these formulas as arrays by pressing CTRL+SHIFT+ENTER.

then use your

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,B2:B3,0))),E2:E5)

Thanks
Tigs

Unfortunately the defining reference in Column C is the text. The numerics are random. Good approach though.

I will keep attacking .... this one has become a nightmare

 

[Aladin Akyurek]

Shouldn't the expected outcome amount to 16, not 13?

 

[Berlina]

Shouldn't the expected outcome amount to 16, not 13?

No 13. Because Col B confirms/checks A2,A3,A4 whose sum(D2:D4)=13. What my objective is sum (C2:C4) in this instance by looking up (ie vlookup) the text ID of inf-011,inf-022,fib-0110 etc to return the answer.
My trouble is sumproduct sums only numbers and damned if I can get a formula working in the last part. I am stuck on this one.

Title List Code Rate
Infini Infini inf-01 1
Infini Fibre inf-02 2
Fibre fib-01 10
Copper
cop-01 20

 

[Aladin Akyurek]

No 13. Because Col B confirms/checks A2,A3,A4 whose sum(D2:D4)=13. What my objective is sum (C2:C4) in this instance by looking up (ie vlookup) the text ID of inf-011,inf-022,fib-0110 etc to return the answer.
My trouble is sumproduct sums only numbers and damned if I can get a formula working in the last part. I am stuck on this one.

Title List Code Rate
Infini Infini inf-01 1
Infini Fibre inf-02 2
Fibre fib-01 10
Copper
cop-01 20

You have 2 infini's under Title. They both match infini under List. And every infini of Title therefore match every inf value under Code. That gives 2 * 1 + 2 * 2 ==> 6. With 10 together 16.

 

[Berlina]

You have 2 infini's under Title. They both match infini under List. And every infini of Title therefore match every inf value under Code. That gives 2 * 1 + 2 * 2 ==> 6. With 10 together 16.

Try the formula =SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,B2:B3,0))),D2:D5)
You get 13. 1+2+10=13.

 

[barry houdini]

I tried =SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,B2:B3,0))),vlookup(C2:C5,D2:D5,1,False))

What results do you expect the VLOOKUP to give in that formula? I don't see what's matching in your example

 

[Aladin Akyurek]

Try the formula =SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,B2:B3,0))),D2:D5)
You get 13. 1+2+10=13.

Are you saying that with this formula you have the solution you need?

 

[Berlina]

What results do you expect the VLOOKUP to give in that formula? I don't see what's matching in your example

The example I have given is simplified version of the data sheets I have - principle is the same. In essence I want (or wish) for in one formula:
=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,B2:B3,0))),C2:C5)
where ,C2:C5) is replaced with a vlookup to get values from the array from D2:D5 eg: =SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,B2:B3,0))),vlookup(C2:C5,D2:D5,1,False)) which obviously gives errors because vlookup value cannot be array (single cell only).

Appreciate your help, really do.