Help with I don't even know what to call it

[segram77]

I have been going nuts trying to figure out what formula to use for this.

I have a spread sheet with data entered something like this

SHEET2
A B C
[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"]first[/TD]
[TD="width: 64"]last[/TD]
[TD="width: 64"]hours[/TD]
[/TR]
[TR]
[TD]frank[/TD]
[TD]smith[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ed[/TD]
[TD]sham[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sara[/TD]
[TD]dart[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]mel[/TD]
[TD]bent[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]hank[/TD]
[TD]bruce[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

and another sheet that I want to be able to paste a report in that when its in there would something look like this

SHEET2
A B C D E F
[TABLE="width: 448"]
<tbody>[TR]
[TD="width: 64"]first[/TD]
[TD="width: 64"]last[/TD]
[TD="width: 64"]program[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]hours[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]frank[/TD]
[TD]smith[/TD]
[TD]hill[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ed[/TD]
[TD]sham[/TD]
[TD]maple[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sara[/TD]
[TD]dart[/TD]
[TD]crest[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]mel[/TD]
[TD]bent[/TD]
[TD]hill[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]hank[/TD]
[TD]bruce[/TD]
[TD]maple[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ed[/TD]
[TD]sham[/TD]
[TD]crest[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I have been trying to find a formula that I can put in sheet1 under the C column that will match the first and last names from each sheet and produce the sum of hours in the C column. therefor if a name is mentioned more than once in SHEET2 then both values for hours will be added and represented as a sum in SHEET1 across from their name. I apologize if I am in the wrong section as I have never posted and will be able to clarify anything if necessary I just am in need of this formula to complement other data in SHEET1 that is not listed.

 

[Dave Patton]

consider
=SUMPRODUCT(--(Sheet2!$A$2:$A$100=A2),--(Sheet2!$B$2:$B$100=B2),(Sheet2!$F$2:$F$100))

Edit the ranges as required

 

[segram77]

consider
=SUMPRODUCT(--(Sheet2!$A$2:$A$100=A2),--(Sheet2!$B$2:$B$100=B2),(Sheet2!$F$2:$F$100))

Edit the ranges as required

Sorry names are not in the same order on both SHEETS

 

[BobUmlas]

=SUMIFS(Sheet!$F$2:$F$100,Sheet2!$A$2:$A$100,A2,Sheet2!$B$2:$B$100,B2)

 

[segram77]

Sorry names are not in the same order on both SHEETS

Its also a list of 200 staff and would like to find a formula I can drag down the cloumn in SHEET1

 

[bkaehny]

Have you considered using a pivot table? That would easily sum the hours, and you could add in a breakout for Program if you so desired.

 

[Rick Rothstein]

You showed both sheets as being SHEET2 which cannot be since both sheets have different data in Columns A:B. Anyway, assuming your list with the hours is really on SHEET2, put this formula in cell C2 on whatever sheet you need the formula in and copy it down (of course, you will have to adjust cell references to match your actual data layout)...

=SUMIFS(SHEET2!F$2:F$7,SHEET2!A$2:A$7,A2,SHEET2!B$2:B$7,B2)


EDIT NOTE: Whoops! I see BobUmlas beat me by 5 minutes with the same formula. No matter, my comments still apply.

 

[segram77]

I have not thought of pivot table. My issue is I get a report on hours used and I do not have control over the order of the names but need the hours for each individual in another sheet I have created. My hopes was developing a formula where I could paste a copy of this report and have the hours pulled to my sheet with other data on it. I think this is possible because while the order of names changes the columns for them and the hours utilized do not.

 

[segram77]

You showed both sheets as being SHEET2 which cannot be since both sheets have different data in Columns A:B. Anyway, assuming your list with the hours is really on SHEET2, put this formula in cell C2 on whatever sheet you need the formula in and copy it down (of course, you will have to adjust cell references to match your actual data layout)...

=SUMIFS(SHEET2!F$2:F$7,SHEET2!A$2:A$7,A2,SHEET2!B$2:B$7,B2)

This works with the first cell but when I go to drag formula produces 0.00 for the rest. any thoughts??

 

[segram77]

Here is the formula D is the hours on sheet 2; B is the last name on sheet 2 and the last name on sheet 1; A is the first name on sheet 2 and C is last name on sheet 1.

=SUMIFS('Sept H'!D$7:D$2000,'Sept H'!B$7:B$2000,B3,'Sept H'!A$7:A$2000,C3)