PDA

View Full Version : Solved: Lookup / Sumproduct Issue



f2e4
01-17-2011, 07:29 AM
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
01-17-2011, 10:44 AM
See Attached.

The Formula is:

=SUMPRODUCT(--NOT(ISERROR(SEARCH($C27,SHEET2!$I$5:$I$24))),--(SHEET2!$C$5:$C$24=$C$5:$C$24),D$5:D$24)

f2e4
01-18-2011, 03:02 AM
See Attached.

The Formula is:

=SUMPRODUCT(--NOT(ISERROR(SEARCH($C27,SHEET2!$I$5:$I$24))),--(SHEET2!$C$5:$C$24=$C$5:$C$24),D$5:D$24)


Superstar!!!

Works perfectly. Thanks for the help

F.

magodey
01-18-2011, 10:30 AM
A GOOD JOB.