how to find each of week of day then add up the numbers?

Question

I have this excel: (for example)

fri, jul 1	sat, jul 2	sun, jul 3	mon, jul 4	tue, jul 5	wed, jul 6	thur, jul 7	fri, jul 8	sat, jul 9	sun, jul 10	mon, jul 11	tue, jul 12	wed, jul 13
$10	$20	$30	$15	$18	$14	$24	$74	$68	$45	$88	$77	$49
total of sundays	today of mondays	total of tues	total of wed	total of thurs	total of fris	total of sat	(empty)	(empty)	(empty)	(empty)	(empty)	(empty)

What I like to do is find each of the week of days (from row1), then sum all its numbers below them (row 2).

in this case, based on the sample table provided:

sum of all suns: 75

sum of all mons: 103

sum of all tues: 95

sum of all wed: 63

sum of all thur: 24

sum of all fri: 84

sum of all sat: 88

anyway I can do this without the manual calculations?

Answer 1

Sum Up By Weekday

=LET(sData,A1:M2,slRow,1,srRow,2,
        dData,{"su";"mo";"tu";"we";"th";"fr";"sa"},
    dl,TOROW(dData,,3),
    sl,LEFT(INDEX(sData,slRow,),LEN(@dl)),
    sr,INDEX(sData,srRow,),
    dr,BYCOL(dl,LAMBDA(c,
        SUM(IFERROR(FILTER(sr,sl=c,0),"")))),
dr)

Answer 2

Here is what you could do dynamically spill an array - LAMBDA( ) is not required :

---

• Formula used in cell O2

=LET(
     a,WRAPROWS(TOCOL(A1:M2,,1),2),
     b,TEXTBEFORE(INDEX(a,,1),", "),
     c,SORT(HSTACK(b,XMATCH(b, 
     {"Sun","Mon","Tue","Wed","Thur","Fri","Sat"}),INDEX(a,,2)),2),
     d,INDEX(c,,1),
     e,UNIQUE(HSTACK("Sum of all "&d,MMULT(N(d=TOROW(d)),INDEX(c,,3)))),e)

---

If you need the header, then add a VSTACK( )

---

=LET(
     a,WRAPROWS(TOCOL(A1:M2,,1),2),
     b,TEXTBEFORE(INDEX(a,,1),", "),
     c,SORT(HSTACK(b,XMATCH(b, 
     {"Sun","Mon","Tue","Wed","Thur","Fri","Sat"}),INDEX(a,,2)),2),
     d,INDEX(c,,1),
     e,UNIQUE(HSTACK("Sum of all "&PROPER(d),
     MMULT(N(d=TOROW(d)),INDEX(c,,3)))),VSTACK({"Weekdays","Total Sum"},e))

---

Or,

---

=LET(
     a,WRAPROWS(TOCOL(A1:M2,,1),2),
     b,TEXTBEFORE(INDEX(a,,1),", "),
     c,SORT(HSTACK(b,XMATCH(b,{"Sun","Mon","Tue","Wed","Thur","Fri","Sat"}),INDEX(a,,2)),2),
     d,INDEX(c,,1),
     e,UNIQUE(HSTACK("Sum of all "&PROPER(d)&":"&
       TEXT(MMULT(N(d=TOROW(d)),INDEX(c,,3)),"_($* #,##0_)"))),
       VSTACK("Weekdays"&":"&"Total Sum",
     e))

---

Answer 3

To find unique week days:

=UNIQUE(TEXTBEFORE(A1:M1,","),1)

To get sums:

=BYCOL(UNIQUE(TEXTBEFORE(A1:M1,","),1),LAMBDA(z,SUM((TEXTBEFORE($A$1:$M$1,",")=z)*($A$2:$M$2))))

Result:

If you need to start it by Sunday then sort array.

Or manually write weekdays in row 3, add this formula to A4:

=SUM((TEXTBEFORE($A$1:$M$1,",")=A3)*($A$2:$M$2))

And drag it right.