Forum Discussion

Hogstad_Raadgivning's avatar
Hogstad_Raadgivning
Iron Contributor
May 14, 2024
Solved

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...
excel
Formulas and Functions
  • djclements's avatar
    djclements
    May 14, 2024

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

Hogstad_Raadgivning's avatar
Hogstad_Raadgivning
Iron Contributor
May 14, 2024
Thank you, I was hoping to avoid the extra column for week number. But that is a solution.
djclements's avatar
djclements
Silver Contributor
May 14, 2024

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

Resources