Excel - Dynamic array function with Offset/Max function

1
votes

I have Excel where I sum data from green table (Table 2) according Column1 and Month (sum is in Column 3). Month in column A is generated by function SEQUENCE and header is taken from blue table. in "B2" is formula =SUMIFS(Table2[Column2];Table2[Column1];B1#;Table2[Month];A2#)

Thanks to the # formula spills to whole range "B2:D13". So far everything is perfect. But now I need to use MAX function to get MAX value for every row. But when I refer to the range by # then it will take the whole range "B2:D13" is there any way, how to spill max formula to every row, but reference only on concrete row?

enter image description here

1
exceldynamic-arrays
did you try MAXIFS? – Scott Craner
It will not work. I would have to use MAXIFS for the green table, if there are multiple values for one cost center and one month then it will take the biggest one not the biggest in the sum... Otherwise this is just example I keep getting same problem elsewhere that is why I need some solution how to differentiate when I am referring the whole range with MAX function and when I want to take just the value in the row but spill the function... – Cetriolo

1 Answers

1
votes

use MAXIFS with OFFSET and SEQUENCE. Put this in E2 and it will spill:

=MAXIFS(OFFSET(B2,SEQUENCE(ROWS(B2#),,0),0,1,3),OFFSET(B2,SEQUENCE(ROWS(B2#),,0),0,1,3),"<>")

enter image description here