Forum Discussion
Solved
Why do some functions spill automaticly, and others not?
Hi, In the attached eacamle there is some function that automaticly fill down the list of names, and others do not. Why is that? The functions in the yellow cells spill: CountIf, SumIf; Sum. Is ...
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#)
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#)