Countifs?

[chaboyski]

Hi Team,

just wondering what formula would i be able to use to do counting of exceptions with multiple criteria?

i needed to count column H criteria based on what time.

heres the data

DV Template.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	First Name	Last Name	ID	Sort Name	Seniority	Code	Description	Rank	Nominal Date	Day of Week	Start	Start Time of Day	Stop	Stop Time of Day	Duration	Memo	Time
2						UPLEXHR: ABSENCE DURING EXHRS	UPLEXHR: ABSENCE DURING EXHRS	27	4/4/2023	Tuesday	07:00 AM	07:00 AM	07:30 AM	07:30 AM	00:30		7:00 AM
3						OP: SYSTEM ISSUES BILLABLE	SYSTEM ISSUES BILLABLE	58	4/4/2023	Tuesday	07:00 AM	07:00 AM	07:19 AM	07:19 AM	00:19		7:00 AM
4						OP: SYSTEM ISSUES BILLABLE	SYSTEM ISSUES BILLABLE	58	4/4/2023	Tuesday	07:00 AM	07:00 AM	07:42 AM	07:42 AM	00:42		7:00 AM
5						UPLEXHR: ABSENCE DURING EXHRS	UPLEXHR: ABSENCE DURING EXHRS	27	4/4/2023	Tuesday	07:00 AM	07:00 AM	07:30 AM	07:30 AM	00:30		7:00 AM
6						UPLEXHR: ABSENCE DURING EXHRS	UPLEXHR: ABSENCE DURING EXHRS	27	4/4/2023	Tuesday	07:00 AM	07:00 AM	07:30 AM	07:30 AM	00:30		7:00 AM
7						UPLEXHR: ABSENCE DURING EXHRS	UPLEXHR: ABSENCE DURING EXHRS	27	4/4/2023	Tuesday	07:05 AM	07:05 AM	07:35 AM	07:35 AM	00:30		7:00 AM
8						UPLEXHR: ABSENCE DURING EXHRS	UPLEXHR: ABSENCE DURING EXHRS	27	4/4/2023	Tuesday	07:05 AM	07:05 AM	07:35 AM	07:35 AM	00:30		7:00 AM
9						UPLEXHR: ABSENCE DURING EXHRS	UPLEXHR: ABSENCE DURING EXHRS	27	4/4/2023	Tuesday	07:05 AM	07:05 AM	07:35 AM	07:35 AM	00:30		7:00 AM
10						UPLEXHR: ABSENCE DURING EXHRS	UPLEXHR: ABSENCE DURING EXHRS	27	4/4/2023	Tuesday	07:05 AM	07:05 AM	07:35 AM	07:35 AM	00:30		7:00 AM
11						UPLTO: UNPLAND TIME OFF(DAY OF	UNPLANED TIME OFF (DAY OF)	22	4/4/2023	Tuesday	07:10 AM	07:10 AM	04:10 PM	04:10 PM	09:00		7:00 AM
12						UPLTO: UNPLAND TIME OFF(DAY OF	UNPLANED TIME OFF (DAY OF)	22	4/4/2023	Tuesday	07:10 AM	07:10 AM	04:10 PM	04:10 PM	09:00		7:00 AM
13						UPLTO: UNPLAND TIME OFF(DAY OF	UNPLANED TIME OFF (DAY OF)	22	4/4/2023	Tuesday	07:10 AM	07:10 AM	08:26 AM	08:26 AM	01:16		7:00 AM
14						OP: SYSTEM ISSUES BILLABLE	SYSTEM ISSUES BILLABLE	58	4/4/2023	Tuesday	07:20 AM	07:20 AM	07:41 AM	07:41 AM	00:21		7:00 AM
15						UPLEXHR: ABSENCE DURING EXHRS	UPLEXHR: ABSENCE DURING EXHRS	27	4/4/2023	Tuesday	07:25 AM	07:25 AM	07:55 AM	07:55 AM	00:30		7:00 AM
16						UPLEXHR: ABSENCE DURING EXHRS	UPLEXHR: ABSENCE DURING EXHRS	27	4/4/2023	Tuesday	07:25 AM	07:25 AM	07:55 AM	07:55 AM	00:30		7:00 AM
Sheet1

Cell Formulas
Range	Formula
G15:Q16,G2:P14	G2	=Dump!G3
R2:R16	R2	=FLOOR(L2,1/24)

heres the output where i would be putting the formula

DV Template.xlsx
	U	V	W	X
1		SYSTEM ISSUES BILLABLE	UPLEXHR: ABSENCE DURING EXHRS	UNPLANED TIME OFF (DAY OF)
2	7:00 AM
3	8:00 AM
4	9:00 AM
5	10:00 AM
6	11:00 AM
7	12:00 PM
8	1:00 PM
9	2:00 PM
10	3:00 PM
11	4:00 PM
12	5:00 PM
13	6:00 PM
14	7:00 PM
15	8:00 PM
16	9:00 PM
17	10:00 PM
Sheet1

Cell Formulas
Range	Formula
U3:U17	U3	=U2+"1:00"

thank you!

 

[awoohaw]

when pasting your mini workbook it came up with a missing link. Please provide the Dump! sheet.

 

[fjns]

Hi, see the linked file for a possible solution...

The formula used in the table:
V2: =COUNTIFS($R:$R,$U2,$H:$H,V$1) (Range:V2:X17)

DVTemplate.xlsx

 

[Fluff]

How about

Fluff.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	First Name	Last Name	ID	Sort Name	Seniority	Code	Description	Rank	Nominal Date	Day of Week	Start	Start Time of Day	Stop	Stop Time of Day	Duration	Memo	Time
2						UPLEXHR: ABSENCE DURING EXHRS	UPLEXHR: ABSENCE DURING EXHRS	27	04/04/2023	Tuesday	7:00 AM	7:00 AM	7:30 AM	7:30 AM	00:30		7:00 AM
3						OP: SYSTEM ISSUES BILLABLE	SYSTEM ISSUES BILLABLE	58	04/04/2023	Tuesday	7:00 AM	7:00 AM	7:19 AM	7:19 AM	00:19		7:00 AM
4						OP: SYSTEM ISSUES BILLABLE	SYSTEM ISSUES BILLABLE	58	04/04/2023	Tuesday	7:00 AM	7:00 AM	7:42 AM	7:42 AM	00:42		7:00 AM
5						UPLEXHR: ABSENCE DURING EXHRS	UPLEXHR: ABSENCE DURING EXHRS	27	04/04/2023	Tuesday	7:00 AM	7:00 AM	7:30 AM	7:30 AM	00:30		7:00 AM
6						UPLEXHR: ABSENCE DURING EXHRS	UPLEXHR: ABSENCE DURING EXHRS	27	04/04/2023	Tuesday	7:00 AM	7:00 AM	7:30 AM	7:30 AM	00:30		7:00 AM
7						UPLEXHR: ABSENCE DURING EXHRS	UPLEXHR: ABSENCE DURING EXHRS	27	04/04/2023	Tuesday	7:05 AM	7:05 AM	7:35 AM	7:35 AM	00:30		7:00 AM
8						UPLEXHR: ABSENCE DURING EXHRS	UPLEXHR: ABSENCE DURING EXHRS	27	04/04/2023	Tuesday	7:05 AM	7:05 AM	7:35 AM	7:35 AM	00:30		7:00 AM
9						UPLEXHR: ABSENCE DURING EXHRS	UPLEXHR: ABSENCE DURING EXHRS	27	04/04/2023	Tuesday	7:05 AM	7:05 AM	7:35 AM	7:35 AM	00:30		7:00 AM
10						UPLEXHR: ABSENCE DURING EXHRS	UPLEXHR: ABSENCE DURING EXHRS	27	04/04/2023	Tuesday	7:05 AM	7:05 AM	7:35 AM	7:35 AM	00:30		7:00 AM
11						UPLTO: UNPLAND TIME OFF(DAY OF	UNPLANED TIME OFF (DAY OF)	22	04/04/2023	Tuesday	7:10 AM	7:10 AM	4:10 PM	4:10 PM	09:00		7:00 AM
12						UPLTO: UNPLAND TIME OFF(DAY OF	UNPLANED TIME OFF (DAY OF)	22	04/04/2023	Tuesday	7:10 AM	7:10 AM	4:10 PM	4:10 PM	09:00		7:00 AM
13						UPLTO: UNPLAND TIME OFF(DAY OF	UNPLANED TIME OFF (DAY OF)	22	04/04/2023	Tuesday	7:10 AM	7:10 AM	8:26 AM	8:26 AM	01:16		7:00 AM
14						OP: SYSTEM ISSUES BILLABLE	SYSTEM ISSUES BILLABLE	58	04/04/2023	Tuesday	7:20 AM	7:20 AM	7:41 AM	7:41 AM	00:21		7:00 AM
15						UPLEXHR: ABSENCE DURING EXHRS	UPLEXHR: ABSENCE DURING EXHRS	27	04/04/2023	Tuesday	7:25 AM	7:25 AM	7:55 AM	7:55 AM	00:30		7:00 AM
16						UPLEXHR: ABSENCE DURING EXHRS	UPLEXHR: ABSENCE DURING EXHRS	27	04/04/2023	Tuesday	7:25 AM	7:25 AM	7:55 AM	7:55 AM	00:30		7:00 AM
Sheet1

Fluff.xlsm
	U	V	W	X
1		SYSTEM ISSUES BILLABLE	UPLEXHR: ABSENCE DURING EXHRS	UNPLANED TIME OFF (DAY OF)
2	07:00:00	3	9	3
3	08:00:00	0	0	0
4	09:00:00	0	0	0
5	10:00:00	0	0	0
6	11:00:00	0	0	0
7	12:00:00	0	0	0
8	13:00:00	0	0	0
9	14:00:00	0	0	0
10	15:00:00	0	0	0
11	16:00:00	0	0	0
12	17:00:00	0	0	0
13	18:00:00	0	0	0
14	19:00:00	0	0	0
15	20:00:00	0	0	0
16	21:00:00	0	0	0
17	22:00:00	0	0	0
Sheet1

Cell Formulas
Range	Formula
V2:X17	V2	=COUNTIFS($H:$H,V$1,$R:$R,$U2)
U3:U17	U3	=U2+"1:00"

 

[chaboyski]

Thank you so much! @Fluff

 

[Fluff]

You're welcome & thanks for the feedback.