Table Pulling Hours Per Person, Per Month

[FightMilk]

Hello! I'm somewhat new to Excel, so I pre-apologize for any ignorance and appreciate your time. I am working on a spreadsheet for a small department where I work to track our time at the company's various locations. I have a setup with a couple of sheets, the first with the information being input, the second with a couple of tables pulling data from the first. I have been trying to get a table working that pulls data on the hours each person in the department spends offsite (away from our main site) during each month. So the data sheet looks something like:

(A) Initials	(B) Date	(C) Time In	(D) Time Out	(E) Hours	(F) Location
AAA	7/16/18	7:32	14:35	7:00	Site#2
BBB	7/19/18	8:43	17:24	8:45	Site#4
AAA	7/22/18	7:01	15:03	8:00	Site#3

<tbody>
</tbody>

So A:F columns are the persons initials, the date, their time in, their time out, an hours column auto calculated and rounded to 15 minute intervals, and finally the location of the site they were at.

I haven't been able to get a working formula to pull the data for each person, for each month. For example, AAA put in 15 hours offsite in July. I have read countless sites and tried countless formulas. I would greatly appreciate your help on this. The formula I have right now is:

=SUMPRODUCT(--(MONTH((Sheet1!$B:$B)=F2)), --(Sheet1!$A:$A="AAA"), Sheet1!$E:$E)

F2 in this is on sheet two and is a cell formatted for the month but is just 1/1/2018 in the cell. The month function in this doesn't seem to be working in this, as it just currently pulls all of the total hours from all months for AAA. Is this a problem with the way I am using the month function? I tried using different already existing cells for the month instead of F2, but that didn't help. Sorry if my mistake is egregious or obvious. Like I've mentioned, I'm fairly new to excel.

Thanks for your time!

 

[Weazel]

If I'm understanding it correctly you are comparing the month in F2 to the month in column B so maybe something like...

=SUMPRODUCT(--(MONTH((Sheet1!$B:$B)=MONTH(F2))), --(Sheet1!$A:$A="AAA"), Sheet1!$E:$E)

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Initials	Date	Time In	Time Out	Hours	Location		Sheet1
2​	AAA	7/16/2018​	7:32​	14:35​	7:00​	Site#2
3​	BBB	7/19/2018​	8:43​	17:24​	8:45​	Site#4
4​	AAA	7/22/2018​	7:01​	15:03​	8:00​	Site#3

<tbody>
</tbody>

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	inital	Hours				month			Sheet2
2​	AAA	15:00:00​				7/1/2018​
3​	BBB	8:45:00​

<tbody>
</tbody>

Sheet2

b2=SUMPRODUCT((Sheet1!$A$2:$A$4=Sheet2!A3)*(MONTH(Sheet1!$B$2:$B$4)=MONTH(Sheet2!$F$2))*(Sheet1!$E$2:$E$4)) copy down

Column B custom format [h]:mm:ss

 

[FightMilk]

If I'm understanding it correctly you are comparing the month in F2 to the month in column B so maybe something like...

=SUMPRODUCT(--(MONTH((Sheet1!$B:$B)=MONTH(F2))), --(Sheet1!$A:$A="AAA"), Sheet1!$E:$E)

Yeah, thats what I am going for. Essentially taking the month in F2 on Sheet2 and then running through the column B on Sheet1 and pulling any data matching the month from Sheet2!F2, if that makes sense. This formula seems to give me the same total hours from ALL months, unfortunately. Thank you though! Maybe I'm doing something wrong with it.

b2=SUMPRODUCT((Sheet1!$A$2:$A$4=Sheet2!A3)*(MONTH(Sheet1!$B$2:$B$4)=MONTH(Sheet2!$F$2))*(Sheet1!$E$2:$E$4)) copy down

Column B custom format [h]:mm:ss

Thank you!! This works until I try to change the A2:A4, B2:B4, and E2:E4 to just A:A, B:B, and E:E respectively. Is there a reason why when I try to just make it apply to the entire column it no longer works? It works if I just designate a specific range in the column, like B2:B25 for example.

Also, out of my own curiosity so I can learn for future excel usage, it looks like between these two formulas the two of you gave, I was using the MONTH formula wrong, yes? It looks like I need to do =MONTH(Range)=MONTH(MonthCelltoSpecifyMonth)
Is that correct?

And again, I appreciate both of your time and help on these issues.