Index, Match and return sum of the matched values

[Nicostin]

Hi there - I am hoping someone can help me with my first post on any forum.

I have a set of data where there are duplicate occurrences of the same person. Each duplicate person has other attributes which make the data unique which is used in another report and I used 'Index, Match' to return values.

My query is that I need to add up the total (hours worked) for all the duplicate matches of the person in another report? Pivots are not an option.

Guessing its possible, but what approach do I need to take?

Any help appreciated. Thanks

 

[Special-K99]

Post a (dummy) spreadsheet.

 

[RonB1111]

Probably SUMIF() function.
See: Using SUMIF to add up cells in Excel that meet certain criteria. Learn | Five Minute Lessons

If you need help formulating it, please post a small sample of your data and the expected results.

 

[Nicostin]

[TABLE="width: 585"]
<tbody>[TR]
[TD="colspan: 2"]
Datta to be returned is the hours
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Weekending[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Names and Rates[/TD]
[TD]Type[/TD]
[TD="align: right"]10-Jul[/TD]
[TD="align: right"]17-Jul[/TD]
[TD="align: right"]24-Jul[/TD]
[TD="align: right"]31-Jul[/TD]
[TD="align: right"]07-Aug[/TD]
[/TR]
[TR]
[TD]Jon[/TD]
[TD]Hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]130[/TD]
[TD]Blended Price Rate[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Cost Rate[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Pete[/TD]
[TD]Hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]130[/TD]
[TD]Blended Price Rate[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Cost Rate[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Ian[/TD]
[TD]Hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]130[/TD]
[TD]Blended Price Rate[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Cost Rate[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Source Data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Projects[/TD]
[TD]Name[/TD]
[TD="align: right"]10-Jul[/TD]
[TD="align: right"]17-Jul[/TD]
[TD="align: right"]24-Jul[/TD]
[TD="align: right"]31-Jul[/TD]
[TD="align: right"]07-Aug[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]Jon[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]Jon[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD]Jon[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]Pete[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]Pete[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]Ian[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[/TR]
</tbody>[/TABLE]

the week's are variable, so don't think SUMIFs really work in this one.

 

[RonB1111]

Enter formula in C2, copy across to G2 (or as far across as you expect to have w/e dates, then copy to C5:G5, then copy to C7:G7:

=SUMIF($B$15:$B20,$A2,C$15:C$20)

If the date columns between the top and bottom table don't line up, let me know how they're actually structured so I can adjust the formula to accomodate.