Calculate maximum of 3 amounts for daily pay but I have the same date on multiple rows

[megera716]

I've got a table of people, procedures they performed by day and the duration of each procedure. Comp structure is such that the employee's pay for that day is the greatest of 3 options:
1) Actual hours worked * $100
2) $750
3) If 3 procedures are performed in a day, $500 for the first one, $250 for the second, $250 for the third. So basically $1000.

That is their pay for the WHOLE DAY so I'm struggling with how to write this so it doesn't try to put the daily pay amount on every row for that day. I am fine if it's easier for the formula to be written to say $375 ($750/2) for Procedure 1 and $375 for Procedure 2 instead of $750 on #1 and $0 on #2. This is extra "fun" (to me) because we're working with times here and I know that can make things wonky. Like if I multiply Employee 1's December 1 total time worked of 4:36 (4h 36m) by $100, the result is $19.17 .

Yes, I did change the formula on Row 6 just to see if I could make that work and that one actually works the best of the three! But it's only for when they do have 3 procedures that day -- notice the $500, $250, $250 on Employee 2 for 12/5/2023 -- but obviously I want one formula to work the whole way down.

I would be eternally grateful to anyone who can help me automate this!

 

[dreid1011]

Maybe this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			Name	Procedure	Date							Start	End	Duration	Pay
2			Emp 1	1	12/1/2023							7:57	10:53	2:56	750
3			Emp 1	2	12/1/2023							12:29	14:09	1:40
4			Emp 1	1	12/19/2023							6:55	8:53	1:58	750
5			Emp 1	2	12/19/2023							11:33	13:37	2:04
6			Emp 2	1	12/5/2023							6:10	7:42	1:32	1000
7			Emp 2	2	12/5/2023							8:32	9:33	1:01
8			Emp 2	3	12/5/2023							10:32	14:04	3:32
Sheet5

Cell Formulas
Range	Formula
O2:O8	O2	=IF(COUNTIFS($C$2:C2,C2,$E$2:E2,E2)=1,MAX((HOUR(N2)+MINUTE(N2)/60+SECOND(N2)/(60*60))*100,750,IF(COUNTIFS($C$2:$C$8,C2,$E$2:$E$8,E2)=3,1000,0)),"")

 

[megera716]

Maybe this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			Name	Procedure	Date							Start	End	Duration	Pay
2			Emp 1	1	12/1/2023							7:57	10:53	2:56	750
3			Emp 1	2	12/1/2023							12:29	14:09	1:40
4			Emp 1	1	12/19/2023							6:55	8:53	1:58	750
5			Emp 1	2	12/19/2023							11:33	13:37	2:04
6			Emp 2	1	12/5/2023							6:10	7:42	1:32	1000
7			Emp 2	2	12/5/2023							8:32	9:33	1:01
8			Emp 2	3	12/5/2023							10:32	14:04	3:32
Sheet5

Cell Formulas
Range	Formula
O2:O8	O2	=IF(COUNTIFS($C$2:C2,C2,$E$2:E2,E2)=1,MAX((HOUR(N2)+MINUTE(N2)/60+SECOND(N2)/(60*60))*100,750,IF(COUNTIFS($C$2:$C$8,C2,$E$2:$E$8,E2)=3,1000,0)),"")

OMG!!!11!! I don't know how it works but it works! If you wouldn't mind breaking it down for me so I can follow it better, I'd be so grateful but I am already so grateful that I have a solution!

Mostly not following the first COUNTIFS and why we're not including the whole range in there (to row 8) or absoluting the end of the range (e.g., it's $C$2:C2, not $C$2:$C$2 or $C$2:$C$8, but the second countifs IS to $C$8 and $E$8). Will this be dependent on the data always being sorted this specific way? (which was by employee, then by date, then by procedure #). If yes, that's fine. Just want to make sure I understand how to apply this to what will be a continuously-growing table.

 

[dreid1011]

OMG!!!11!! I don't know how it works but it works! If you wouldn't mind breaking it down for me so I can follow it better, I'd be so grateful but I am already so grateful that I have a solution!

Mostly not following the first COUNTIFS and why we're not including the whole range in there (to row 8) or absoluting the end of the range (e.g., it's $C$2:C2, not $C$2:$C$2 or $C$2:$C$8, but the second countifs IS to $C$8 and $E$8). Will this be dependent on the data always being sorted this specific way? (which was by employee, then by date, then by procedure #). If yes, that's fine. Just want to make sure I understand how to apply this to what will be a continuously-growing table.

=IF(COUNTIFS($C$2:C2,C2,$E$2:E2,E2)=1,MAX((HOUR(N2)+MINUTE(N2)/60+SECOND(N2)/(60*60))*100,750,IF(COUNTIFS($C$2:$C$8,C2,$E$2:$E$8,E2)=3,1000,0)),"")

The first COUNTIFS allows us to identify which row to display the result on, letting us find where the first unique value starts for each employee/date combo. By leaving the end of the range open (not absolute), the range will increase as the formula is dragged down, therefor limiting the area to the first row through the current row. See the example below to see that by limiting the range, we can get an incremental count instead of the total count for the whole range:

Book1
	A	B	C
1	Name	COUNT open	COUNT absolute
2	A	1	3
3	A	2	3
4	A	3	3
5	B	1	4
6	B	2	4
7	B	3	4
8	B	4	4
9	C	1	1
Sheet6

Cell Formulas
Range	Formula
B2:B9	B2	=COUNTIF($A$2:A2,A2)
C2:C9	C2	=COUNTIF($A$2:$A$9,A2)

The second COUNTIF is using the entire range as absolute so we can see if the total count is 3, as seen in the above example. Also, if we locked the range at $C$2:$C$2 it would only ever look at row 2 no matter how far it is dragged down.

It should not matter how the data is sorted.

You're welcome, and happy to help.

 

[megera716]

=IF(COUNTIFS($C$2:C2,C2,$E$2:E2,E2)=1,MAX((HOUR(N2)+MINUTE(N2)/60+SECOND(N2)/(60*60))*100,750,IF(COUNTIFS($C$2:$C$8,C2,$E$2:$E$8,E2)=3,1000,0)),"")

The first COUNTIFS allows us to identify which row to display the result on, letting us find where the first unique value starts for each employee/date combo. By leaving the end of the range open (not absolute), the range will increase as the formula is dragged down, therefor limiting the area to the first row through the current row. See the example below to see that by limiting the range, we can get an incremental count instead of the total count for the whole range:

Book1
	A	B	C
1	Name	COUNT open	COUNT absolute
2	A	1	3
3	A	2	3
4	A	3	3
5	B	1	4
6	B	2	4
7	B	3	4
8	B	4	4
9	C	1	1
Sheet6

Cell Formulas
Range	Formula
B2:B9	B2	=COUNTIF($A$2:A2,A2)
C2:C9	C2	=COUNTIF($A$2:$A$9,A2)

The second COUNTIF is using the entire range as absolute so we can see if the total count is 3, as seen in the above example. Also, if we locked the range at $C$2:$C$2 it would only ever look at row 2 no matter how far it is dragged down.

It should not matter how the data is sorted.

You're welcome, and happy to help.

Fantastic, thank you so much for the original solution AND the added explanation!