If, sumif, sumproduct????

[cr2crf]

Good morning and thanks in advance for your help.

I am trying to figure out what function to use and how to write a formula that will tell me If Bill Bob worked during week 1, and his name appears on the list of those that worked (sheet1!A:A), and that week has four 8 hour days as indicated in the cell Sheet2!C2, credit him with 40 hours in C4. Bob's name can appear on any row in any given week so I used the A:A to signify anywhere in the column.

Here is my crude example of my worksheet:

Sheet1 (Columns A-D, Rows 1-7)
[TABLE="width: 612"]
<tbody>[TR]
[TD]WEEK 1 (H)[/TD]
[TD]WEEK 2[/TD]
[TD]WEEK 3[/TD]
[TD]WEEK 4[/TD]
[/TR]
[TR]
[TD]30-Dec-17[/TD]
[TD]6-Jan-18[/TD]
[TD]13-Jan-18[/TD]
[TD]20-Jan-18[/TD]
[/TR]
[TR]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[TD]to[/TD]
[/TR]
[TR]
[TD]5-Jan-18[/TD]
[TD]12-Jan-18[/TD]
[TD]19-Jan-18[/TD]
[TD]26-Jan-18[/TD]
[/TR]
[TR]
[TD]OFFICERS[/TD]
[TD]OFFICERS[/TD]
[TD]OFFICERS[/TD]
[TD]OFFICERS[/TD]
[/TR]
[TR]
[TD]Bill Bob[/TD]
[TD]Carl Credit[/TD]
[TD]Don Diamond[/TD]
[TD]Carl Credit[/TD]
[/TR]
[TR]
[TD]Don Diamond[/TD]
[TD][/TD]
[TD][/TD]
[TD]Bill Bob[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2 (Columns A-G, Rows 1-6)

[TABLE="width: 501"]
<tbody>[TR]
[TD]R = 5, H = 4[/TD]
[TD][/TD]
[TD]H[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CURRENT[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BANK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bill Bob[/TD]
[TD]154[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carl Credit[/TD]
[TD]154[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Don Diamond[/TD]
[TD]154[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am using Excel 2016 on Office 365.

Any help would be appreciated!

 

[cr2crf]

Anyone?

 

[NiMip]

I'm a bit confused about the 40 hours part (wouldn't 4 x 8hour shifts be 32?), but the simplest version of what you've asked for would be, in cell C4, =IF(ISNUMBER(MATCH(A4,Sheet1!A:A,0)),40,0)

If you want the 40 to be calculated somehow, then just replace the 40 with another equation. If you want to be able to select the week, then I could add a week number input to this as well.

 

[cr2crf]

Great. I had to adjust it a bit but that did it. Many thanks.