Home » Sum Numbers in Zigzag Path

Sum Numbers in Zigzag Path

Find the sum of numbers traversing the diagonal from first column to second column to third column to second column to first column and repeating it till the end. The path is shown in yellow.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 623
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Sum Numbers in Zigzag Path with Power Query

Power Query solution 1 for Sum Numbers in Zigzag Path, proposed by Kris Jaganah: 
let
  A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  B = Table.ToColumns(A), 
  C = List.Positions(B{0}), 
  D = List.Max(C) + 1, 
  E = List.FirstN(List.Repeat({0, 1, 2, 1}, D), D), 
  F = List.Sum(List.Transform(List.Zip({E, C}), each B{_{0}}{_{1}}))
in
  F
Power Query solution 2 for Sum Numbers in Zigzag Path, proposed by Abdallah Ally: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  ToRows = Table.ToRows(Source), 
  Result = List.Sum(
    List.Transform(
      {0 .. Table.RowCount(Source) - 1}, 
      each ToRows{_}{List.Min({Number.Mod(_, 4), 4 - Number.Mod(_, 4)})}
    )
  )
in
  Result
Power Query solution 3 for Sum Numbers in Zigzag Path, proposed by Ramiro Ayala Chávez: 
let
  S   = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  LT  = List.Transform, 
  LA  = List.Alternate, 
  LS  = List.Sum, 
  LC  = List.Combine, 
  col = Table.ColumnCount(S), 
  a   = List.Split(Table.ToRows(S), col - 1), 
  b   = LT(a, LC), 
  c   = LA(b, 1, 1, 1), 
  d   = List.Difference(b, c), 
  e   = LT(d, each List.Split(_, col)), 
  f   = LT(e, each LT(_, List.Reverse)), 
  g   = LT(f, LC), 
  h   = LT(c, each LS(LA(_, col, 1, 1))), 
  i   = LT(g, each LS(LA(_, col, 1, 1))), 
  Sol = Table.FromValue(LS(h & i), [DefaultColumnName = "Answer Expected"])
in
  Sol
Power Query solution 4 for Sum Numbers in Zigzag Path, proposed by Seokho MOON: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  Res = [
    A = List.Combine(Table.ToRows(Source)), 
    B = List.Split(A, 12), 
    C = List.Transform(B, (x) => List.Transform({0, 4, 8, 10}, each x{_}?)), 
    D = List.Sum(List.Combine(C))
  ][D]
in
  Res
Power Query solution 5 for Sum Numbers in Zigzag Path, proposed by Meganathan Elumalai: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Result = [
    Lst = Table.ToRows(Source), 
    fin = List.Sum(
      List.Transform(
        {0 .. List.Count(Lst) - 1}, 
        (f) => if Number.Mod(f, 4) = 3 then Lst{f}{1} else Lst{f}{Number.Mod(f, 4)}
      )
    )
  ][fin]
in
  Result
Power Query solution 6 for Sum Numbers in Zigzag Path, proposed by Rafael González B.: 
let
 Source = Question_Table,
 Rows = Table.ToRows(Source),
 Ct = List.Count(Rows),
 LR = {0,1,2,1},
 Pt = List.FirstN(List.Repeat(LR, Number.RoundUp(Ct/List.Count(LR))),Ct),
 Zip = List.Accumulate(
 List.Zip({Rows, Pt}),
 0,
 (s,c) => s + c{0}{c{1}})
in
 Zip

🧙🏻‍♂️🧙🏻‍♂️🧙🏻‍♂️
Power Query solution 7 for Sum Numbers in Zigzag Path, proposed by Ben Warshaw: 
let
  Source = Excel.CurrentWorkbook(){[Name = "tbl"]}[Content], 
  ToCol = Table.ToColumns(Source), 
  Accum = List.Accumulate(
    List.Numbers(0, 18), 
    [x = 0, y = 0, z = 0], 
    (s, c) => [
      x = if Number.Mod(c, 4) = 0 then ToCol{0}{c} + s[x] else s[x], 
      y = if Number.Mod(c, 2) = 1 then ToCol{1}{c} + s[y] else s[y], 
      z = if Number.Mod(c, 4) = 2 then ToCol{2}{c} + s[z] else s[z]
    ]
  ), 
  Sum = Accum[x] + Accum[y] + Accum[z]
in
  Sum

Solving the challenge of Sum Numbers in Zigzag Path with Excel

Excel solution 1 for Sum Numbers in Zigzag Path, proposed by Bo Rydobon 🇹🇭: 
=LET(z,
    A2:C19,
    s,
    SEQUENCE(
        ROWS(
            z
        )
    ),
    SUM(INDEX(z,
    s,
    SCAN(2,
    -(-1^INT(
        s/2
    )),
    SUM))))

Dynamic columns

=LET(z,
    A2:D19,
    c,
    COLUMNS(
            z
        )-1,
    s,
    SEQUENCE(
        ROWS(
            z
        )
    ),
    SUM(INDEX(z,
    s,
    SCAN(2,
    -1^INT((s-2)/c),
    SUM))))
Excel solution 2 for Sum Numbers in Zigzag Path, proposed by John V.: 
=SUM((IF(
    1+A2:C21,
    {1,
    2,
    3}
)=TOCOL(
    {1,
    2,
    3,
    2}*ROW(
        1:5
    )^0
))*A2:C21)

or

✅
=LET(
    i,
    A2:C19,
    r,
    ROW(
        i
    )-1,
    m,
    MOD(
        r,
        4
    ),
    SUM(
        INDEX(
            i,
            r,
            IF(
                m,
                m,
                2
            )
        )
    )
)
Excel solution 3 for Sum Numbers in Zigzag Path, proposed by Kris Jaganah: 
=LET(a,A2:C19,b,SEQUENCE(ROWS(a)),c,MOD(b,COLUMNS(a)+1),SUM(INDEX(a,b,IF(c,c,2))))
Excel solution 4 for Sum Numbers in Zigzag Path, proposed by Julian Poeltl: 
=LET(N,A2:C19,SUM(N*MAKEARRAY(ROWS(N),COLUMNS(N),LAMBDA(A,B,OR(MOD((A+B)-6,4)=0,ISEVEN(B)*ISEVEN(A))))))
Excel solution 5 for Sum Numbers in Zigzag Path, proposed by Aditya Kumar Darak 🇮🇳: 
=LET(
 _data, A2:C19,
 _i, SEQUENCE(ROWS(_data)),
 _cols, COLUMNS(_data),
 _j, _cols - ABS(MOD(_i - 1, _cols + 1) - _cols + 1),
 _v, INDEX(_data, _i, _j),
 _rtrn, SUM(_v),
 _rtrn
)
Excel solution 6 for Sum Numbers in Zigzag Path, proposed by Timothée BLIOT: 
=SUM(IFERROR(REDUCE("",ROW(1:99),LAMBDA(w,v,VSTACK(w,IF(ISEVEN(v),{0,1,0},MUNIT(3)))))*A1:C19,0))
Excel solution 7 for Sum Numbers in Zigzag Path, proposed by Hussein SATOUR: 
=LET(a,A2:C19,b,ROWS(a),s,SEQUENCE(b),c,TAKE(TOCOL((s/3)^0*{1,2,3,2}),b),SUM(MAP(s,c,LAMBDA(x,y,INDEX(a,x,y)))))
Excel solution 8 for Sum Numbers in Zigzag Path, proposed by Oscar Mendez Roca Farell: 
=LET(
    d,
    A2:C19,
    SUM(
        IF(
            MOD(
                SEQUENCE(
                    ROWS(
                        d
                    )
                )-{1,
                0,
                -1},
                {4,
                2,
                4}
            ),
            ,
            d
        )
    )
)
Excel solution 9 for Sum Numbers in Zigzag Path, proposed by Duy Tùng: 
=LET(a,A2:C19,b,MOD(ROW(a),4),SUM(a*N(HSTACK(b-2=0,ISODD(b),b=0))))
Excel solution 10 for Sum Numbers in Zigzag Path, proposed by Sunny Baggu: 
=LET(
    
     _s,
     SEQUENCE(
         ROWS(
             A2:C19
         )
     ),
    
     _c,
     MOD(
         _s,
          2
     ) = 0,
    
     _a,
     TOCOL(
         IF(
             _c,
              _s,
              1 / x
         ),
          3
     ),
    
     _b,
     UNIQUE(
         TOCOL(
             _a + {-1,
              1}
         )
     ),
    
     SUM(
         
          INDEX(
              A2:C19,
               _a,
               2
          ),
         
          TOCOL(
              INDEX(
                  A2:C19,
                   WRAPROWS(
                       _b,
                        2
                   ),
                   {1,
                   3}
              ),
               3
          )
          
     )
    
)
Excel solution 11 for Sum Numbers in Zigzag Path, proposed by Sunny Baggu: 
=LET(
    
     _s,
     SEQUENCE(
         ROWS(
             A2:C19
         )
     ),
    
     _a,
     MOD(
         _s,
          4
     ),
    
     _b,
     1 - MOD(
         _s,
          2
     ),
    
     _c,
     BYROW(
         HSTACK(
             _a,
              _b
         ) = 1,
          LAMBDA(
              a,
               1 - XOR(
                   a
               )
          )
     ),
    
     SUM(
         IF(
             HSTACK(
                 _a,
                  _b,
                  _c
             ) = 1,
              A2:C19,
              0
         )
     )
    
)
Excel solution 12 for Sum Numbers in Zigzag Path, proposed by Sunny Baggu: 
=LET(
 _a, WRAPROWS(SEQUENCE(15, , 1, 4), 3),
 _b, TAKE(_a, , -1) + 2,
 SUM(
 TOCOL(
 INDEX(TOCOL(A2:C19), VSTACK(TOCOL(_a), _b)),
 3
 )
 )
)
Excel solution 13 for Sum Numbers in Zigzag Path, proposed by LEONARD OCHEA 🇷🇴: 
=LET(d,A2:C19,s,SEQUENCE(ROWS(d)),SUM(INDEX(d,s,MOD(s^3+2*s^2+2,4))))
Excel solution 14 for Sum Numbers in Zigzag Path, proposed by Md. Zohurul Islam: 
=LET(a,A2:C19,
r,ROWS(a),
b,SEQUENCE(r),
c,COLUMNS(a),
d,ROUNDUP(c/2,0),
e,REPT(CONCAT(SEQUENCE(,c),d),r/c-1),
f,TAKE(MID(e,SEQUENCE(LEN(e)),1),r),
g,INDEX(a,b,f),
h,SUM(g),
h)
Excel solution 15 for Sum Numbers in Zigzag Path, proposed by Pieter de B.: 
=LET(s,SEQUENCE(18),m,MOD(s-1,4)+1,SUM(INDEX(A2:C19,s,IF(m=4,2,m))))

Or
=LET(a,A2:C19,s,ROW(a)-1,m,MOD(s-1,4)+1,SUM(INDEX(a,s,IF(m=4,2,m))))
Excel solution 16 for Sum Numbers in Zigzag Path, proposed by Pieter de B.: 
=LAMBDA(a,
    SUM(MAP(a,
    LAMBDA(m,
    (GET.CELL(
        63,
        m
    )=6)*m))))

Then use: =YELLOW(
    A2:C19
)
Excel solution 17 for Sum Numbers in Zigzag Path, proposed by ferhat CK: 
=LET(a,MAP(A2:A19,LAMBDA(x,MOD(ROW(x)-1,4))),b,IF(a=0,2,a),SUM(MAKEARRAY(ROWS(b),1,LAMBDA(r,c,CHOOSECOLS(CHOOSEROWS(A2:C19,r),CHOOSEROWS(b,r))))))
Excel solution 18 for Sum Numbers in Zigzag Path, proposed by Jaroslaw Kujawa: 
=REDUCE(
    0;
    A2:A19;
    LAMBDA(
        a;
        x;
        a+IF(
            MOD(
                ROW(
                    x
                )-2;
                4
            )=0;
            SUM(
                OFFSET(
                    x;
                    {1,2,3,4}-1;
                    {1,2,3,2}-1;
                    4;
                    3
                )
            );
            0
        )
    )
)
Excel solution 19 for Sum Numbers in Zigzag Path, proposed by Meganathan Elumalai: 
=LET(
    a,
    A2:C19,
    c,
    COLUMNS(
        a
    )+1,
    s,
    SEQUENCE(
        ROWS(
        a
    )
    ),
    SUM(
        INDEX(
            a,
            s,
            IF(
                MOD(
                    s,
                    c
                ),
                MOD(
                    s,
                    c
                ),
                2
            )
        )
    )
)
Excel solution 20 for Sum Numbers in Zigzag Path, proposed by Milan Shrimali: 
=LET(SUMCOLM1,SUM(BYROW(SEQUENCE(COUNTA(A2:A19),1,2,4),LAMBDA(X,IFERROR(FILTER(A2:A19,ROW(A2:A19)=X),0)))),SUMCOLM2,SUM(BYROW(B2:B19,LAMBDA(X,IFERROR(FILTER(X,MOD(ROW(X),2)=1),0)))),SUMCOLM3,SUM(BYROW(SEQUENCE(COUNTA(C2:C19),1,4,4),LAMBDA(X,IFERROR(FILTER(C2:C19,ROW(C2:C19)=X),0)))),SUM(SUMCOLM1,SUMCOLM2,SUMCOLM3))
Excel solution 21 for Sum Numbers in Zigzag Path, proposed by Nicolas Micot: 
=LET(_indexs_et_increments;SCAN("0;1";SEQUENCE(LIGNES(A2:C19));LAMBDA(l_init;l_tableau;LET(_valeur;TEXTE.AVANT(l_init;";")+0;_increment;TEXTE.APRES(l_init;";")+0;_nbCol;COLONNES(A2:C19);_new_valeur;_valeur+_increment;_new_valeur&";"&SI(_new_valeur=1;1;SI(_new_valeur=_nbCol;-1;_increment)))));
_indexs;TEXTE.AVANT(_indexs_et_increments;";")+0;
SOMME(INDEX(A2:C19;SEQUENCE(LIGNES(A2:C19));_indexs)))
Excel solution 22 for Sum Numbers in Zigzag Path, proposed by Cary Ballard, DML: 
=SUM(CHOOSECOLS(WRAPROWS(TOCOL(A2:C19),12,0),1,5,9,11))
Excel solution 23 for Sum Numbers in Zigzag Path, proposed by Ernesto Vega Castillo: 
=SUM(LET(x,A2:C19,y,SEQUENCE(ROWS(x)),z,MOD(y,4),INDEX(x,y,IF(z,z,2))))
Excel solution 24 for Sum Numbers in Zigzag Path, proposed by Ricardo Romero Garcia: 
=SUMA(LET(x;
    SECUENCIA(
        54
    );
    
 a;
    SECUENCIA(
        15;
        1;
        1;
        4
    );
    
 b;
    SECUENCIA(
        5;
        1;
        11;
        12
    );
    
 c;
    APILARV(
        a;
        b
    );
    
 ENCOL(
     A2:C19
 )*(BUSCARX(
     x;
     c;
     c;
     0
 )>0)))

Solving the challenge of Sum Numbers in Zigzag Path with Python

_x000D_

Python solution 1 for Sum Numbers in Zigzag Path, proposed by Konrad Gryczan, PhD:
 
import pandas as pd
import numpy as np
path = "623 Sum of Numbers Across Diagonals of 3 Columns.xlsx"
input_data = pd.read_excel(path, usecols="A:C", nrows=19).values
test = pd.read_excel(path, usecols="E", nrows=1).squeeze()
def construct_zigzag(n, width=3):
 return np.tile(np.concatenate([np.arange(1, width + 1), np.arange(width - 1, 1, -1)]), n)[:n]
zigzag = construct_zigzag(len(input_data))
result = np.sum(input_data[np.arange(len(input_data)), zigzag - 1])
print(result == test) # True

_x000D_

_x000D_

Python solution 2 for Sum Numbers in Zigzag Path, proposed by Abdallah Ally:
 

import pandas as pd
file_path = 'Excel_Challenge_623 - Sum of Numbers Across Diagonals of 3 Columns.xlsx'
df = pd.read_excel(io=file_path, usecols='A:C', skiprows=1, header=None)
# Perform data manipulation
result = sum(df.iat[i, min(i % 4, 4 - (i % 4))] for i in df.index)
print(f'nResult: {result}')


_x000D_

Solving the challenge of Sum Numbers in Zigzag Path with Python in Excel


_x000D_

Python in Excel solution 1 for Sum Numbers in Zigzag Path, proposed by Alejandro Campos:
 

matrix = xl("A2:C19").values
total_sum = sum(row[i % 4 if i % 4 != 3 else 1] for i, row in enumerate(matrix))


_x000D_

_x000D_

Python in Excel solution 2 for Sum Numbers in Zigzag Path, proposed by Aditya Kumar Darak 🇮🇳:
 

data = xl("A2:C19").values
shp = data.shape
rows = np.arange(shp[0])
cols = shp[1] - abs(rows % (shp[1] + 1) - shp[1] + 1) - 1
result = sum(data[rows, cols])
result


_x000D_

Solving the challenge of Sum Numbers in Zigzag Path with R


_x000D_

R solution 1 for Sum Numbers in Zigzag Path, proposed by Konrad Gryczan, PhD:
 

library(tidyverse)
library(readxl)
path = "Excel/623 Sum of Numbers Across Diagonals of 3 Columns.xlsx"
input = read_excel(path, range = "A1:C19") %>% as.matrix()
test = read_excel(path, range = "E1:E2") %>% pull()
construct_zigzag <- function(n, width = 3) {
 repeating_block <- c(1:width, (width - 1):2)
 rep_len(repeating_block, n)
}
zigzag = construct_zigzag(nrow(input))
result = sum(input[cbind(1:nrow(input), zigzag)])
test == result
#> [1] TRUE


_x000D_
&&

Leave a Reply