=A3+(SUMPRODUCT(
((Sheet1!$B$2:$B$35=J4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*J5)+
((Sheet1!$B$2:$B$35=K4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*K5)+
((Sheet1!$B$2:$B$35=L4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*L5)+
((Sheet1!$B$2:$B$35=M4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*M5)+
((Sheet1!$B$2:$B$35=N4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*N5)+
((Sheet1!$B$2:$B$35=O4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*O5)))
This is entered into A4, and copied down. It's actually a lot longer than this. I can turn 3 of the components above into named ranges. It looks like I can turn J4-O4 and J5-O5 into ranges as well, but when I try to I get errors.
Any options on how to shorten this formula?
((Sheet1!$B$2:$B$35=J4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*J5)+
((Sheet1!$B$2:$B$35=K4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*K5)+
((Sheet1!$B$2:$B$35=L4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*L5)+
((Sheet1!$B$2:$B$35=M4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*M5)+
((Sheet1!$B$2:$B$35=N4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*N5)+
((Sheet1!$B$2:$B$35=O4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*O5)))
This is entered into A4, and copied down. It's actually a lot longer than this. I can turn 3 of the components above into named ranges. It looks like I can turn J4-O4 and J5-O5 into ranges as well, but when I try to I get errors.
Any options on how to shorten this formula?