Selectively sum values in a table based on a value in a second table?

[msilano]

Hi all. Having a difficult (for me) problem trying to summarize some data.

Excel spreadsheet attached, but here's the summary:

I'm trying to sum all hours associated with people who are assigned a specific role.

For instance, I want to be able to display all time associated with resources who are assigned the role "TA".

Any help would be *greatly* appreciated.

Time Table:

Date Person Hours Cost
2009-05-04 Mike Smith 3.0 $750
2009-05-05 John Doe 4.0 $1,000
2009-05-06 Alice Jones 3.0 $750
2009-05-07 Alice Jones 8.0 $1,600
2009-05-08 John Doe 8.0 $2,000
2009-05-09 Tiger Woods 5.0 $1,200

Role Table:

Person Role
Alice Jones PA
John Doe TA
Mike Smith PM
Tiger Woods TA


I've tried a number of solutions (in addition to poring over the forums, the internet, Excel help, and anything else I can think of:

Tried {=SUMPRODUCT((C2:C7)*(VLOOKUP(B2:B7,I3:J6,2,FALSE)="TA"))}
Tried {=SUM(IF(VLOOKUP(B2:B7,I3:J6,2,FALSE)="TA",C2:C7,0))}
Tried {=SUMPRODUCT((C2:C7)*(INDEX(I3:J6,MATCH(B2:B7,I3:I6,0),2)="PM"))}


Thanks much,

Mike

[ratcat]

G'day Mike and welcome to the forum.

See attached should help you out.

Cheers

RC

[msilano]

Ratcat,

Thank you very much.

Only one problem - i was trying to do that without adding another column.

Prolly should have mentioned that in my response.

Is there any easy way to do it with a lookup?

Thanks,

Mike

[royUK]

A PivotTable is the simplest way

[teylyn]

I've tried a number of solutions (in addition to poring over the forums, the internet, Excel help, and anything else I can think of:

Tried {=SUMPRODUCT((C2:C7)*(VLOOKUP(B2:B7,I3:J6,2,FALSE)="TA"))}
Tried {=SUM(IF(VLOOKUP(B2:B7,I3:J6,2,FALSE)="TA",C2:C7,0))}
Tried {=SUMPRODUCT((C2:C7)*(INDEX(I3:J6,MATCH(B2:B7,I3:I6,0),2)="PM"))}

Just FYI: Sumproduct does not need to be confirmed with CSE.

[msilano]

Thanks to all that replied. Was trying to avoid pivot tables, but may be the best way.

Cheers,

Mike

[royUK]

Why avoid PivotTables? Combined with a Dynamic Named Range they are much more efficient than attempting to use Array Formulas or even SumProduct.