Why do some functions spill automaticly, and others not?

[Geir Hogstad]

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 it possible to make Sumproduct .

Update, Maxifs, solved find the largest number problem. So Its sumproduct thats the question.

Please see attached excample.

Best Regards

- Geir

[#2]

@Geir Hogstad 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.

[#3 · Accepted solution]

@Geir Hogstad 

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#)