Sum IF, Index Matching formula not working as intended

Question

Copy Table Here

I am trying to sum numbers based on hourly and per line. I want create a summary table below for each line hourly total output. The formula I have entered in Cell B36 is =SUMIF($E$2:$L$2,"="&B35,INDEX($E$3:$L$9,MATCH($A36,$A$3:$A$9,0),0)) But its showing me 0 instead of 15 for 1st hour, 60 for 2 Hour, 20 for 3 Hour, 45+37 for 4th Hour, and so on.

I would really appreciate if you guys tell me what i did wrong? or what I can do to fix this.

Answer 1

Try:

---

• Formula used in cell B36

=SUM(
    FILTER(
        FILTER(
            $E$3:$L$28,
            SCAN(
                $A$3:$A$28,
                $A$3:$A$28,
                LAMBDA(x, y,
                    IF(
                        y = "",
                        x,
                        y
                    )
                )
            ) = $A36
        ),
        B$35 = $E$2:$L$2
    )
)

---

You can also use a single dynamic array formula here as well using REDUCE()

---

• Formula used in cell B36

=DROP(REDUCE("",A36:A47,LAMBDA(a,b,
VSTACK(a,BYCOL(B35:I35,LAMBDA(c,
SUM(FILTER(FILTER(E3:L28,
SCAN($A$3:$A$28,$A$3:$A$28,LAMBDA(x,y,IF(y="",x,y)))=b,""),c=E2:L2,""))))))),1)