Assistance with Index Match formula

[Jay88]

I have the following formular which looks up a fortnightly roster and matches each name and role, summing the total shift length that is in ROSTER!$C$4:$C$800 rows under each name for each shift.

It works fine, though I am wondering if it is possible to reduce repetitions/the formular length by using a nested array of some sorts? I am using Microsoft Excel 2019 LTSC.

All matches each need to reference $C$4:$C$800 offsetting down 2 rows (shift length).

All matches each need to reference AC$4:$AC$800 = $KI2 (position name)

Each day of the roster, all look up $D2 (employee name)

G$4:G$800, H$4:H$800, I$4:I$800, J$4:J$800, K$4:K$80,L$4:L$800, M$4:M$800, U$4:U$800, V$4:V$800, W$4:W$800, X$4:X$800, Y$4:Y$800, Z$4:Z$800,AA$4:AA$800

=SUM(
    IFNA(INDEX(ROSTER!$C$4:$C$800,MATCH(1,(ROSTER!G$4:G$800=$D2)*(ROSTER!$AC$4:$AC$800=$KI2),0)+2),0),
    IFNA(INDEX(ROSTER!$C$4:$C$800,MATCH(1,(ROSTER!H$4:H$800=$D2)*(ROSTER!$AC$4:$AC$800=$KI2),0)+2),0),
    IFNA(INDEX(ROSTER!$C$4:$C$800,MATCH(1,(ROSTER!I$4:I$800=$D2)*(ROSTER!$AC$4:$AC$800=$KI2),0)+2),0),
    IFNA(INDEX(ROSTER!$C$4:$C$800,MATCH(1,(ROSTER!J$4:J$800=$D2)*(ROSTER!$AC$4:$AC$800=$KI2),0)+2),0),
    IFNA(INDEX(ROSTER!$C$4:$C$800,MATCH(1,(ROSTER!K$4:K$800=$D2)*(ROSTER!$AC$4:$AC$800=$KI2),0)+2),0),
    IFNA(INDEX(ROSTER!$C$4:$C$800,MATCH(1,(ROSTER!L$4:L$800=$D2)*(ROSTER!$AC$4:$AC$800=$KI2),0)+2),0),
    IFNA(INDEX(ROSTER!$C$4:$C$800,MATCH(1,(ROSTER!M$4:M$800=$D2)*(ROSTER!$AC$4:$AC$800=$KI2),0)+2),0),
    IFNA(INDEX(ROSTER!$C$4:$C$800,MATCH(1,(ROSTER!U$4:U$800=$D2)*(ROSTER!$AC$4:$AC$800=$KI2),0)+2),0),
    IFNA(INDEX(ROSTER!$C$4:$C$800,MATCH(1,(ROSTER!V$4:V$800=$D2)*(ROSTER!$AC$4:$AC$800=$KI2),0)+2),0),
    IFNA(INDEX(ROSTER!$C$4:$C$800,MATCH(1,(ROSTER!W$4:W$800=$D2)*(ROSTER!$AC$4:$AC$800=$KI2),0)+2),0),
    IFNA(INDEX(ROSTER!$C$4:$C$800,MATCH(1,(ROSTER!X$4:X$800=$D2)*(ROSTER!$AC$4:$AC$800=$KI2),0)+2),0),
    IFNA(INDEX(ROSTER!$C$4:$C$800,MATCH(1,(ROSTER!Y$4:Y$800=$D2)*(ROSTER!$AC$4:$AC$800=$KI2),0)+2),0),
    IFNA(INDEX(ROSTER!$C$4:$C$800,MATCH(1,(ROSTER!Z$4:Z$800=$D2)*(ROSTER!$AC$4:$AC$800=$KI2),0)+2),0),
    IFNA(INDEX(ROSTER!$C$4:$C$800,MATCH(1,(ROSTER!AA$4:AA$800=$D2)*(ROSTER!$AC$4:$AC$800=$KI2),0)+2),0)
)

Here is what the roster looks like

 

[DanteAmor]

Try the following array formula

=SUM(IF((Roster!G$4:AA$800=$D2)*(Roster!$AC$4:$AC$800=$KI2),Roster!$C$6:$C$802))

Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Jay88]

Try the following array formula

=SUM(IF((Roster!G$4:AA$800=$D2)*(Roster!$AC$4:$AC$800=$KI2),Roster!$C$6:$C$802))

Press CTRL+SHIFT+ENTER to enter array formulas.

Dante that worked perfectly, thank you!