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