Help with formula to calculate sum after searching multiple colums

[Lcollins]

Hopefully someone can shed some light. I am trying to findthe number of hours a person worked for the month. In my spreadsheet I want theformula to look through the range for cells where staff were assigned to work(Columns E-J, Rows 2-8). Each time a particular person is named in that range(ie... David in cells F3 and E6) I would like the formula to add the hoursworked in column D. David worked 8 hours on Jan 1 and 7 hours on Jan 3.<o></o>
<o></o>
<o></o>

Date<o></o>	Customer<o></o>	# of<o></o> staff<o></o>	hrs <o></o> worked<o></o>	Staff utilized<o></o>
1-Jan<o></o>	Mr. Davis<o></o>	1<o></o>	8<o></o>	Tim<o></o>
1-Jan<o></o>	Mrs. Jones<o></o>	2<o></o>	5<o></o>	John<o></o>	David<o></o>
2-Jan<o></o>	Coolridge Apts<o></o>	5<o></o>	8<o></o>	William<o></o>	Bobby<o></o>	Brian<o></o>	Jim<o></o>	Dave<o></o>
3-Jan<o></o>	Hamilton HOA<o></o>	4<o></o>	6<o></o>	Brian<o></o>	Sal<o></o>	Billy<o></o>	John<o></o>
3-Jan<o></o>	Meadow Lark Apts<o></o>	6<o></o>	7<o></o>	David<o></o>	Tom<o></o>	Will<o></o>	Mike<o></o>	John<o></o>
4-Jan<o></o>	Mr. Williams<o></o>	3<o></o>	3.5<o></o>	Tim<o></o>	Jim<o></o>	John<o></o>
5-Jan<o></o>	City Park<o></o>	6<o></o>	10<o></o>	John<o></o>	Dave<o></o>	Mark<o></o>	Will<o></o>	Mike<o></o>	Jim<o></o>

<tbody>
</tbody>

<o></o>

 

[snjpverma]

*	C	D
12	David	12

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:61px;"><col style="width:76px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D12 =SUMPRODUCT(D2:D8*(E2:J8=C12)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Lcollins]

I'll admit I don't understand the formula you provided. When I plug the formula into a cell is says David worked 97.5 hours. Maybe I did something wrong.

I'm looking for a formula which will look through all of the staff who worked during the time frame (cells in the range of E2 to J8) and find the name "David" in each of those cells. Each time "David" is listed in the cell range of E2 through J8, I want the formula to take the hours worked on that particular day (found the F column of the corresponding row) and add them together. David is listed as working in cells F3 and E6, showing he worked on Jan 1 and Jan 3. I would like the formula to then look at the corresponding number of hours worked each of those days (5 hrs on Jan 1 - cell D3 and 7 hrs on Jan 3 - cell D6). David, thus worked 12 hrs. Does this make sense?

Using the same logic, John worked 5 times during this time frame for 31.5 hrs.

 

[snjpverma]

When I put david in C12 it gives me 12 and 31.5 for John. I think you haven't selected the proper cell.
The below highlighted part of the formula has to be adjusted according to your data.
You can either give a reference to a cell where the name of a person whose data has to be summed or write David or any person's name manually in your formula as shown in below formula.

=SUMPRODUCT(D2:D8*(E2:J8=C12)) OR =SUMPRODUCT(D2:D8*(E2:J8="David"))

<tbody>
</tbody>

 

[Lcollins]

Got it! Thank you very much

 

[snjpverma]

Got it! Thank you very much

You are welcome