SOLVED

# Two way dynamic sum (by column and rows)

Steel Contributor

# Two way dynamic sum (by column and rows)

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?

Best regards

Geir

3 Replies

# Re: Two way dynamic sum (by column and rows)

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

# Re: Two way dynamic sum (by column and rows)

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

# Re: Two way dynamic sum (by column and rows)

@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

# Re: Two way dynamic sum (by column and rows)

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