Hopefully this makes sense to anyone reading this.
To get the results I needed I used helper columns. To keep it simple, I'm just going to use Group 1 and Group 2. Including Group 3 and Group 4 required additional helper columns, same as Group 1 and Group 2.
First, I broke out the date (e.g. 1/1/2025) into year (e.g. 2025) and week (e.g. 1):
=YEAR(I3)
=WEEKNUM(I3)
Second, I concatenated the two results (e.g. 1-2025):
=K3&"-"&J3
Third, for each group I used a logical Or for comparing the subgroups:
For Group 1 =IF(OR(A3>0,B3>0),1,0)
For Group 2 =IF(OR(C3>0,D3>0),1,0)
Output is "1" if the subgroups contain values greater than "0".
Fourth, for each group I used the MAXIFS formula to grab the highest value of each group within each week:
For Group 1 =MAXIFS($M$3:$M$97,$L$3:$L$97,L3)
For Group 2 =MAXIFS($O$3:$O$97,$L$3:$L$97,L3)
Fifth, I summed the outputs of each groups MAXIFS formula (e.g. Group 1 and Group 2):
=SUM(N3,P3)
Sixth, I calculated the weekly MAX number across all Groups:
=MAXIFS($Q$3:$Q$11,$L$3:$L$11,L3)
Seventh, using the UNIQUE formula I mapped out the Week and Year results into a separate column:
=UNIQUE(L3:L11)
And for the final step, I performed a vLookup to pull in the MAXIFS value of each Week...
For the first week of 1-2025: =VLOOKUP(S3,$L:$R,7,0)
For the 2nd week of 6-2025: =VLOOKUP(S4,$L:$R,7,0)
For the 3rd week of 7-2025: =VLOOKUP(S5,$L:$R,7,0)
