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.

 

[Berlina]

Imagine Sheet1 only had A to C column to work with. And Sheet2 has C and D column data.

Have I totally confused you all. Sorry.

 

[Aladin Akyurek]

Correct A2,A3 and A4 match B2:B3. The result is in my nightmare sheet column C (ie C2,C3,C4 --- which are ID codes like shown). I want to within the formula lookup these ID codes C2,C3,C4 from another sheet (for the example shown I stuck the D column in). So ultimately C2=1,C3=2,C4=10 and the sum is 13.

We need a procedure that correlates the items (results of matching A2:A4 against B2:B3) to items in C2:C5. Are you claiming that:

Infini (position 1 in A2:A5), matching an item in B2:B3 --> yes, take the item at position 1 in C2:C5

Infini (position 2 in A2:A5), matching an item in B2:B3 --> yes, take the item at position 2 in C2:C5

Fiber (position 3 in A2:A5), matching an item in B2:B3 --> yes, take the item at position in C2:C5

Copper (position 4 A2:A5), matching an item in B2:b3 --> no, take nothing from C2:C5

Is this the procedure you want to implement?

 

[Berlina]

We need a procedure that correlates the items (results of matching A2:A4 against B2:B3) to items in C2:C5. Are you claiming that:

Infini (position 1 in A2:A5), matching an item in B2:B3 --> yes, take the item at position 1 in C2:C5

Infini (position 2 in A2:A5), matching an item in B2:B3 --> yes, take the item at position 2 in C2:C5

Fiber (position 3 in A2:A5), matching an item in B2:B3 --> yes, take the item at position in C2:C5

Copper (position 4 A2:A5), matching an item in B2:b3 --> no, take nothing from C2:C5

Is this the procedure you want to implement?

Exactly. And then lookup the results of position 1,2,3 only of C2:C5 and reference them to & sum position 1,2,3 of D2:D5.

Remembering column D and matching cell ID's of column C are in another sheet.

 

[Aladin Akyurek]

Exactly. And then lookup the results of position 1,2,3 only of C2:C5 and take & sum position 1,2,3 of D2:D5.

Remembering column D and matching cell ID's of column C are in another sheet.

Since C2:C5 correlates with D2:D5, there is no need for C2:C5 at all. Right? If so,
care to provide some other example for which this procedure would work?

 

[Berlina]

There is a need for C2:C5 because in my full version cells in column C are dropdown lists with text ID codes. I therefore need to ultimately correlate these codes to other lists which have both the ID codes and values in matching rows.

 

[Berlina]

any recommendations of sites for me to upload a xls sample file for you to retrieve?

 

[Aladin Akyurek]

There is a need for C2:C5 because in my full version cells in column C are dropdown lists with text ID codes. I therefore need to ultimately correlate these codes to other lists which have both the ID codes and values in matching rows.

Berlina,

This looks like what you want...

<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=384><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Title</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>List</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>Code</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>Rate</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Infini</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Infini</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>inf-01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>13</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Infini</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Fibre</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>inf-02</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Fibre</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>fib-01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64 align=right>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Copper</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>cop-01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64 align=right>20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2757144 class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR></TBODY></TABLE>

F2, control+shift+enter, not just enter:

=SUM(SUMIF(C2:C5,IF(ISNUMBER(MATCH(ROW(C2:C5),
     IF(ISNUMBER(MATCH(A2:A5,B2:B3,0)),
        ROW(A2:A5)),0)),C2:C5),D2:D5))

However, I'm not entirely convinced of that procedure...

 

[Berlina]

Thankyou Aladin ---- that works. So VERY appreciated. I will try it on my master document.

Berlina,

This looks like what you want...

<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=384><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Title</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>List</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>Code</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>Rate</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Infini</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Infini</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>inf-01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>13</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Infini</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Fibre</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>inf-02</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Fibre</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>fib-01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64 align=right>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Copper</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>cop-01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64 align=right>20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2757144 class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR></TBODY></TABLE>

F2, control+shift+enter, not just enter:

=SUM(SUMIF(C2:C5,IF(ISNUMBER(MATCH(ROW(C2:C5),
     IF(ISNUMBER(MATCH(A2:A5,B2:B3,0)),
        ROW(A2:A5)),0)),C2:C5),D2:D5))

However, I'm not entirely convinced of that procedure...

 

[T. Valko]

Thankyou Aladin ---- that works. So VERY appreciated. I will try it on my master document.

=SUM(SUMIF(C2:C5,IF(ISNUMBER(MATCH(ROW(C2:C5),
IF(ISNUMBER(MATCH(A2:A5,B2:B3,0)),
ROW(A2:A5)),0)),C2:C5),D2:D5))

This seems to do the same thing...

=SUMPRODUCT(SUMIF(A2:A5,B2:B3,D2:D5))

But, I haven't read the entire thread.

 

[barry houdini]

There is a need for C2:C5 because in my full version cells in column C are dropdown lists with text ID codes. I therefore need to ultimately correlate these codes to other lists which have both the ID codes and values in matching rows.

That sounds to me like my earlier suggestion with SUMIF would suit, i.e.

=SUMPRODUCT(--ISNUMBER(MATCH(A2:A5,B2:B3,0)),SUMIF(Y2:Y10,C2:C5,Z2:Z10))

In that formula effectively for any row where the A2:A5 value matches one of B2:B3 the C2:C5 value will be looked up in a table in Y2:Z10 where Y2:Y10 contains the matches with C2:C5 and Z2:Z10 the corresponding values.

Y2:Y10 and Z2:Z10 correspond to your "other lists" so change the cell references accordingly