Solved: Lookup / Sumproduct Issue

[f2e4]

I have 3 tables, as shown in the attached sheet.

SHEET2 is simply a database of details for each project. There is always mulitple 'Funders' per projects, as shown in column I.

SHEET 1 contains two tables - the top table contains the Year to Date and Life to Date costs for each project. The bottom table will be a summary of the total cost per Funder.

Problem:

There is multiple 'Funders' per project and I need to calculate the total costs incurred for each Funder (i.e. sum the YTD_gross values for every project that 'Funder 01' is involved in and then the same for the YTD_Net values and so on). I know there will be double counting but this can be ignored.

Can you someone help me with the formula for SHEET1!D27?

[arkusM]

See Attached.

The Formula is:
[VBA]
=SUMPRODUCT(--NOT(ISERROR(SEARCH($C27,SHEET2!$I$5:$I$24))),--(SHEET2!$C$5:$C$24=$C$5:$C$24),D$5$24)
[/VBA]

[f2e4]

See Attached.

The Formula is:
[vba]
=SUMPRODUCT(--NOT(ISERROR(SEARCH($C27,SHEET2!$I$5:$I$24))),--(SHEET2!$C$5:$C$24=$C$5:$C$24),D$5$24)
[/vba]

Superstar!!!

Works perfectly. Thanks for the help

F.

[magodey]

A GOOD JOB.