Simplify google sheet formula "SUM / INDEX / MATCH"

Question

I am trying to use google sheet to create a roster formula, to sum up the duty hour per week using INDEX/MATCH/SUM.

But it's too long, is there any way to simplify the formula?
Also, I realize "MATCH" cannot recognize blank cell (N20), can that be fixed too?

=IFERROR(SUM(INDEX($O$12:$O$20,MATCH(D17,$N$12:$N$20,0)),INDEX($O$12:$O$20,MATCH(E17,$N$12:$N$20,0)),INDEX($O$12:$O$20,AND(F17,$N$12:$N$20,0)),INDEX($O$12:$O$20,MATCH(G17,$N$12:$N$20,0)),INDEX($O$12:$O$20,MATCH(H17,$N$12:$N$20,0)),INDEX($O$12:$O$20,MATCH(I17,$N$12:$N$20,0)),INDEX($O$12:$O$20,MATCH(J17,$N$12:$N$20,0))),"Err")

Answer 1

Franco, since your post says your end goal is to "sum up the duty hour per week," I take that to mean all you need in the end is a single number.

Try this (which will give you total hours for your block that runs B6:H22:

=ArrayFormula(SUM(COUNTIF(B6:H22,$L$1:$L$8)*$M$1:$M$8))

If you need to see the breakdown per code, you can use this:

=ArrayFormula({$L$1:$L$8,COUNTIF(B6:H22,$L$1:$L$8)*$M$1:$M$8})

Just replace "B6:H22" with the reference of each calendar block to get the sum or the breakdown for other weeks.

Answer 2

try:

=ARRAYFORMULA(MMULT(IFERROR(REGEXREPLACE(UPPER(B6:H14), "^"&TEXTJOIN("$|^", 1, L1:L10)&"$",
 VLOOKUP(REGEXEXTRACT(UPPER(B6:H14), "^"&TEXTJOIN("$|^", 1, L1:L10)&"$"), L1:M10, 2*
 SIGN(ROW(A6:A14)), 0)&""), UPPER(B6:H14))*1, TRANSPOSE(COLUMN(B:H))^0))