Formula which includes all results matching multiple criteria (no spilling)

[Rob__13]

Hi All,

I'm looking for a formula which can search a column and pull out all the relevant values based on 2 criteria, collating them all into one cell.  I have tried the following:

- Lookup - only finds the first value, but i need to find all

- Pivot table - I want this as part of a wider table which has values which can be manually entered (pivot can't do this)

I've provided an example below and would appreciate anyone's solutions!

Results table
Item Code	Description	Cost		Formula should pick up …
A01	UK	[ formula ]		245
A01	France	[ formula ]		37
A01	Spain	[ formula ]		13
A02	UK	[ formula ]		102
A02	France	[ formula ]		111
A02	Spain	[ formula ]		60

Target table
Description 1	Description 2	Values
A01	UK	10
A01	UK	20
A01	UK	50
A01	UK	65
A01	France	11
A01	France	26
A01	Spain	13
A02	UK	2
A02	UK	63
A02	UK	26
A02	UK	11
A02	France	65
A02	France	26
A02	France	20
A02	Spain	10
A02	Spain	50
A01	UK	100

[#2 · Accepted solution]

@Rob__13 

=SUMPRODUCT(($A$12:$A$28=A3)*($B$12:$B$28=B3)*$C$12:$C$28)

You can try this formula.

 

[#3]

Perfect, thank you!

[#4]

@Rob__13 Simple SUMIFS() should give you desired result.

=SUMIFS(L:L,J:J,A2,K:K,B2)