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
1
2		Retention Register
3		Vendor	Invoice No.	Code	Contract Amount	Retention Held		Contractor	Total
4		ABC Contractors	00002		$ 110,000.00	$ 500.00		ABC Contractors	1500
5		DEF CONTRACTORS	DEF0001		$ 80,000.00	$ 2,500.00
6		ABC Contractors	0003		$ 80,000.00	$ 1,500.00
7		ABC Contractors	0003		$ 80,000.00	$ 1,500.00
8		HIJ Supplier	0003		$ 120,000.00	$ 6,000.00
9
10
11
12

<tbody>
</tbody>

Payment Certificate​

Worksheet Formulas Cell Formula S4 =INDEX(RetentionRegister, SMALL(IF(L4:L54=R4,ROW(L4:L54)),ROW(1:1)),5) <tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To RetentionRegister ='Payment Certificate'!$L$4:$P$54 <tbody> </tbody>

<tbody>
</tbody>

 

[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!