Sum maximums matching two other variables

[sultanofding]

Hello,
I am trying to create a function to look up a subject number and the sum the 'Days on o2' where it the maximum for matched start dates. ie patient 001-001, sum the max where the start date is 11/6/20 (2), 11/5/20 (2), 11/8 (4), 11/11 (2) - 10 in total, essentially to not double count entries with the same start date.

 

[Eric W]

Welcome to the MrExcel forum!

Here's one way:

Book2
	A	B	C	D	E	F
1	Subject	Start Date	End Date	Days on O2		Sum of max per date
2	001-001	11/6/2020	11/6/2020	1		10
3	001-001	11/6/2020	11/6/2020	1
4	001-001	11/6/2020	11/7/2020	2
5	001-001	11/7/2020	11/7/2020	1
6	001-001	11/7/2020	11/8/2020	2
7	001-001	11/8/2020	11/11/2020	4
8	001-001	11/11/2020	11/12/2020	2
9	001-002	11/14/2020				0
10	001-003	12/10/2020	12/12/2020	3		13
11	001-003	12/12/2020	12/13/2020	2
12	001-003	12/13/2020	12/14/2020	2
13	001-003	12/14/2020	12/16/2020	3
14	001-003	12/16/2020	12/18/2020	3
15	001-003	12/18/2020
16
Sheet1

Cell Formulas
Range	Formula
F2:F15	F2	=IF(A2=A1,"",SUMPRODUCT(IFERROR(MAXIFS(D:D,A:A,A2,B:B,B2:B30)/COUNTIFS(A:A,A2,B:B,B2:B30),0)))
Press CTRL+SHIFT+ENTER to enter array formulas.

I suspect there's something better though, I'll look at it some more.