Sum of Numbers across diagonal in a Square Matrix - Excel and M-Code

Today we are going to discuss about numerous methods in Excel and Power Query to sum the numbers across diagonal in a square matrix.

Excel Formula:

Method-1:

=SUM(MUNIT(ROWS(A1:H8))*A1:H8)

Method-2:

LET(a,A1:H8,b,SEQUENCE(ROWS(a)),SUM(INDEX(a,b,b)))

Method-3:

=REDUCE(0,SEQUENCE(ROWS(A1:H8)),LAMBDA(a,v,a+INDEX(A1:H8,v,v)))

Method-4:

=LET(a,A1:H8,b,ROWS(a),SUM(MAKEARRAY(b,b,LAMBDA(r,c,IF(r=c,INDEX(a,r,c),0)))))

M-Code:

Method-5:

let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    Result =

        let

            L = Table.ToRows( Source ),

            T = {0..List.Count(L) - 1},

            Trans = List.Sum(List.Accumulate(T, {}, (s,c) => s & {L{c}{c}}) )

        in

    Trans

in

    Result

Method-6:

let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    Result =

        let

            Lst = Table.ToRows( Source ),

            Cnt = {0..List.Count(Lst) - 1},

            Trans = List.Sum( List.Transform(Cnt, (f) => Lst{f}{f}))

        in

    Trans

in

    Result

Method-7:

let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    L = Table.ToRows( Source ),

    Result = List.Sum( List.Generate(() => [Num = L{0}{0}, Cnt = 0],each [Cnt] < List.Count(L),

  each [Cnt = [Cnt] + 1, Num = L{Cnt}{Cnt}], each [Num]) )

in

    Result

Method-8:

let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    Transform = Table.TransformRows( Table.AddIndexColumn( Source,"Index", 0,1),

  (f) => [L = Record.ToList(f), Last = List.Last(L), Num = L{Last}][Num]),

    Result = List.Sum( Transform )

in

    Result