1

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

Cetriolo
  • 41
  • 7
  • did you try MAXIFS? – Scott Craner Oct 13 '20 at 15:47
  • 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 Oct 13 '20 at 15:54

1 Answers1

2

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

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • This might be exactly what I am looking for :) It never occurs to me use the sequence function to spill the formula. Thanks a lot! – Cetriolo Oct 13 '20 at 16:12
  • 2
    Stumbled across this and I think the new `BYROW` formula with `LAMBDA` makes this a lot easier: `=BYROW(B2#,LAMBDA(array,MAX(array)))` – BigBen Oct 22 '21 at 23:37
  • (context was [this question](https://stackoverflow.com/q/69655057/9245853)... where I'm sure you can beat me to a worthy answer). – BigBen Oct 22 '21 at 23:43
  • Yeah BYROW will resolve it perfectly :) I have the feeling this function was not available when writing this post :) – Cetriolo Jun 03 '23 at 15:09