SOLVED

Two way dynamic sum (by column and rows)

Steel Contributor

Hi,

 

What ie the best and easiest way to sum dynamically by both rows and columns? This sums automaticall by rows, how to expand so it does it for each column? 

 

2 way sum.png

 

Best regards 

 

Geir 

3 Replies

@Geir Hogstad SUMIFS is the easiest method, but it requires physical range references. Alternatively, MAKEARRAY can be used with arrays; however, it is less efficient with larger datasets. See attached...

Thank you, I was hoping to avoid the extra column for week number. But that is a solution.
best response confirmed by Geir Hogstad (Steel Contributor)
Solution

@Geir Hogstad No worries. If you want to avoid the extra calculated column, the MAKEARRAY version should do the trick.

 

However, another option would be to send both the product and week number lists to a single column (repeated) and process the results using either MAP or BYROW. Then WRAPROWS can be used to output the final 2-D array:

 

=WRAPROWS(
    MAP(
        TOCOL(IF(SEQUENCE(, COLUMNS(H14#)), G15#)),
        TOCOL(IF(SEQUENCE(ROWS(G15#)), H14#)),
        LAMBDA(n,p, SUM((ISOWEEKNUM(t_uke[Dato])=n)*(t_uke[Produkt]=p)*t_uke[Omsetning]))
    ),
    COLUMNS(H14#)
)

 

-OR-

 

=WRAPROWS(
    BYROW(
        TOCOL(G15# & "|" & H14#),
        LAMBDA(np, SUM((ISOWEEKNUM(t_uke[Dato]) & "|" & t_uke[Produkt] = np)*t_uke[Omsetning]))
    ),
    COLUMNS(H14#)
)

 

See attached...

1 best response

Accepted Solutions
best response confirmed by Geir Hogstad (Steel Contributor)
Solution

@Geir Hogstad No worries. If you want to avoid the extra calculated column, the MAKEARRAY version should do the trick.

 

However, another option would be to send both the product and week number lists to a single column (repeated) and process the results using either MAP or BYROW. Then WRAPROWS can be used to output the final 2-D array:

 

=WRAPROWS(
    MAP(
        TOCOL(IF(SEQUENCE(, COLUMNS(H14#)), G15#)),
        TOCOL(IF(SEQUENCE(ROWS(G15#)), H14#)),
        LAMBDA(n,p, SUM((ISOWEEKNUM(t_uke[Dato])=n)*(t_uke[Produkt]=p)*t_uke[Omsetning]))
    ),
    COLUMNS(H14#)
)

 

-OR-

 

=WRAPROWS(
    BYROW(
        TOCOL(G15# & "|" & H14#),
        LAMBDA(np, SUM((ISOWEEKNUM(t_uke[Dato]) & "|" & t_uke[Produkt] = np)*t_uke[Omsetning]))
    ),
    COLUMNS(H14#)
)

 

See attached...

View solution in original post