Lookup multiple instances of the same value in a range

[Mojoswoptops]

Hi all, I'm trying to lookup every instance of a value (Vendor Name) and return the total sum of each of these rows value from another column (Retention Held).

For example - When ABC Contractor is selected in cell R4, I want every instance of ABC Contractors on the Retention Register to be picked up, and the Retention Held to add up, with the sum of the values from column P for each matching instance from column K, to appear in cell S4.

As you can see, I have tried a few INDEX functions, combined with SMALL functions which I have found on this forum and others, but nothing seems to be working.

I have no issue with doing this in VBA if it's more simple.

Thanks very much!


Excel 2016 (Windows) 32 bit

	K	L	M	N	O	P	Q	R	S
Vendor	Invoice No.	Code	Contract Amount	Retention Held	Contractor	Total
ABC Contractors	ABC Contractors
DEF CONTRACTORS	DEF0001
ABC Contractors
ABC Contractors
HIJ Supplier

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Retention Register[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]00002[/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 110,000.00[/TD]
[TD="align: right"] $ 500.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1500[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 80,000.00[/TD]
[TD="align: right"] $ 2,500.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0003[/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 80,000.00[/TD]
[TD="align: right"] $ 1,500.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0003[/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 80,000.00[/TD]
[TD="align: right"] $ 1,500.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0003[/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 120,000.00[/TD]
[TD="align: right"] $ 6,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Payment Certificate​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]S4[/TH]
[TD="align: left"]=INDEX(RetentionRegister, SMALL(IF(L4:L54=R4,ROW(L4:L54)),ROW(1:1)),5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]RetentionRegister[/TH]
[TD="align: left"]='Payment Certificate'!$L$4:$P$54[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[alansidman]

Have you tried to use the =Sumifs function. Look here for syntax

MS Excel: How to use the SUMIFS Function (WS)

 

[Mojoswoptops]

Thanks so much Alan - that worked perfectly!