Trying to add hours with multiple variable in between 2 dates

[Jennifer4Dillon]

Trying to sum hours for employees. Need to sum by person, in between 2 dates and adding 3 categories. Tried to use sumif and if combined, but haven't been able to get the formula to work. I have other areas of the time sheet that only had one category that I was able to use the sumif. =SUMIFS('Raw Logs'!$E$2:$E$39,'Raw Logs'!$F$2:$F$39,">="&$C3,'Raw Logs'!$F$2:$F$39,"<="&$D3,'Raw Logs'!$C$2:$C$39,"int-9",'Raw Logs'!$H$2:$H$39,$B3)

But when I needed to do INT-1, INT-14 and INT-15, I can't get the formula to work, I have tried combining with IF but couldn't not get to work without error. If I just use another sumif, the total is too high and it just adds it instead of using it as another criteria. I would really appreciate if something was able to help. Thank you.

 

[Toadstool]

Hi Jennifer4Dillon,

Could you explain what "INT-1, INT-14 and INT-15" means?
Can you give a worked example with your expected results?

 

[Jennifer4Dillon]

sorry yes, those another criteria (They are vacation hours, sick hours and holiday hours). They are what they client has named the different types of hours from another sheet in the workbook. I need to sum those types of hours if they fall between the date range and they are the correct sales person. If Match C3 and between D4 and E4, and then sum them by using the criteria.

I have ended up using a workaround by adding 3 columns to the 1st spreadsheet and then summing them and hiding them, but I am very frustrated that I couldn't figure it out. this is the formula I used that worked with one of them, but I couldn't make it work with 3. also here is a screen shot of the 2 sheet. =SUMIFS('Raw Logs'!$E:$E,'Raw Logs'!$F:$F,">="&$C4,'Raw Logs'!$F:$F,"<="&$D4,'Raw Logs'!$C:$C,"int-1",'Raw Logs'!$H:$H,$B4)

 

[Toadstool]

Hi Jennifer4Dillon,

A few comments:

1. The two screenshots don't match in terms of numbers so I've created my own.
2. I would prefer not to hard code the INT-nn into formulae so I'm using row 1 (which could be hidden). You don't say what each INT code is so I guessed.
3. I am generous, so gave Employees 5th July as a holiday.
4. I assumed "Logged" was all INT codes so just used the wildcard asterisk.
5. Take note when checking a date which includes time. Your ""<="&$D4" would look for entries up to 31 July 2021 at 00:00 but Excel holds times as a fraction of a day so I've added 0.999988 which will search up to 31 July 2021 at 23:59:59.

So here's my example Raw Logs

Jennifer4Dillon.xlsx
	A	B	C	D	E	F	G	H
1			INT		Hours	Date		Employee
2			INT-1		8	7/2/2021 10:00		Bob
3			INT-1		8	7/9/2021 10:00		Bob
4			INT-1		8	7/15/2021 8:00		Phil
5			INT-1		8	7/16/2021 8:00		Jerry
6			INT-1		8	7/9/2021 14:10		Bob
7			INT-10		3	7/2/2021 8:45		Jerry
8			INT-12		1	7/13/2021 12:30		Jerry
9			INT-13		1	7/2/2021 10:00		Phil
10			INT-14		8	7/9/2021 10:00		Jerry
11			INT-14		8	7/15/2021 8:00		Bob
12			INT-18		1	7/16/2021 8:00		Jerry
13			INT-2		1	7/9/2021 14:10		Bob
14			INT-2		1	7/2/2021 8:45		Bob
15			INT-4		1	7/13/2021 12:30		Jerry
16			INT-4		1	7/2/2021 10:00		Phil
17			INT-4		1	7/9/2021 10:00		Bob
18			INT-4		1	7/15/2021 8:00		Jerry
19			INT-12		1	7/16/2021 8:00		Jerry
20			INT-4		1	7/9/2021 14:10		Phil
21			INT-4		1	7/2/2021 8:45		Bob
22			INT-6		1	7/13/2021 12:30		Jerry
Raw Logs

And here's my report screen

Jennifer4Dillon.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1						*	INT-13	INT-1	INT-14	INT-15			Holidays
2		Employee	Date Start	Date End	Expected	Logged	Production	Internal	PTO	Sales			7/5/2021
3		Jerry	7/1/2021	7/31/2021	168	25	0	8	8	0
4		Bob	7/1/2021	7/31/2021	168	36	0	24	8	0
5		Phil	7/1/2021	7/31/2021	168	11	1	8	0	0
6
7
8
Report

Cell Formulas
Range	Formula
E3:E5	E3	=NETWORKDAYS(C3,D3,$M$2:$M$17)*8
F3:J5	F3	=SUMIFS('Raw Logs'!$E:$E,'Raw Logs'!$F:$F,">="&$C3,'Raw Logs'!$F:$F,"<="&$D3+0.999988,'Raw Logs'!$C:$C,F$1,'Raw Logs'!$H:$H,$B3)