Forum Discussion

Hogstad_Raadgivning's avatar
Hogstad_Raadgivning
Steel Contributor
May 14, 2024

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
Steel 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
    Bronze 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