Forum Discussion
Why do some functions spill automaticly, and others not?
Geir, if return the spill of sums like
when MMULT is usually used. In this case, in addition to avoid INDIRECT(), formula could be
=MMULT(--INDEX(A_selgere,XMATCH(C5#,A_selgere[Selger],0),SEQUENCE(1,XMATCH($D$2, A_selgere[#Headers])-1,2)),SEQUENCE(XMATCH($D$2, A_selgere[#Headers])-1,1,1,0))Double dash is needed to convert blank cells to zero, otherwise MMULT returns an error.
To spill the max use usual MAXIFS, FILTER is not required
=MAXIFS(A_ordre[Omsetning],A_ordre[Selger],C5#)
Hogstad_Raadgivning This is what the MS support pages write about SUMPRODUCT
The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays.
And that's exactly what it does. A sum formula returns a single value, not an array of values. In E5, the FILTER function returns an array (1 row, 3 columns) based on a criteria (Jan:Mar for sales person Hansen) and the SUMPRODUCT sums up the filtered values. By the way, in this particular example where you only sum one range, you can also just use SUM. It gives the same result.
Revised your file to demonstrate how it would work.