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)

WEEK 1 (H)	WEEK 2	WEEK 3	WEEK 4
30-Dec-17	6-Jan-18	13-Jan-18	20-Jan-18
to	to	to	to
5-Jan-18	12-Jan-18	19-Jan-18	26-Jan-18
OFFICERS	OFFICERS	OFFICERS	OFFICERS
Bill Bob	Carl Credit	Don Diamond	Carl Credit
Don Diamond			Bill Bob

<tbody>
</tbody>

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

R = 5, H = 4		H	R	R	R	R
	CURRENT	4	5	5	5	5
	BANK
Bill Bob	154
Carl Credit	154
Don Diamond	154

<tbody>
</tbody>

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.