Can you help me shorten this formula?

[mrblister]

=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?

 

[Aladin Akyurek]

Is this what you are after?

=A3+SUMPRODUCT(--ISNUMBER(MATCH(Sheet1!$B$2:$B$35,J4:O4,0)),--(Sheet1!$N$2:$N$35=E$4),--ISNUMBER(MATCH(Sheet1!$S$2:$S$35,J5:O5,0)))

 

[Eric W]

Maybe:

=A3+SUM(SUMIFS(Sheet1!$S$2:$S$35,Sheet1!$N$2:$N$35,E$4,Sheet1!$B$2:$B$35,J4:O4)*J5:O5)
Confirmed with Control+Shift+Enter

 

[Marcelo Branco]

Or maybe this

=A3+SUMPRODUCT(IFERROR(INDEX(J5:O5,MATCH(T(IF({1},Sheet1!$B$2:$B$35)),J4:O4,0)),0),--(Sheet1!$N$2:$N$35=E$4),Sheet1!$S$2:$S$35)

Remark: assumes the values in Sheet1!$B$2:$B$35 and in J4:O4 are text. If they are numbers substitute T by N

M.

 

[Marcelo Branco]

Hi Eric

Nice formula!

To avoid the Ctrl+Shift+Enter i think you can use
=A3+SUMPRODUCT(SUMIFS(Sheet1!$S$2:$S$35,Sheet1!$N$2:$N$35,E$4,Sheet1!$B$2:$B$35,J4:O4)*J5:O5)

Well done!

M.

 

[Rick Rothstein]

Hi Eric

Nice formula!

To avoid the Ctrl+Shift+Enter i think you can use
=A3+SUMPRODUCT(SUMIFS(Sheet1!$S$2:$S$35,Sheet1!$N$2:$N$35,E$4,Sheet1!$B$2:$B$35,J4:O4)*J5:O5)

I think you can get rid of the SUMIFS function call as well. This seems to return the same results...

=A3+SUMPRODUCT((Sheet1!$B$2:$B$35=J4:O4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*J5:O5)

 

[Marcelo Branco]

Rick

It seems to me that the two formulas, yours and Eric's, are identical.

M.

 

[Rick Rothstein]

Rick

It seems to me that the two formulas, yours and Eric's, are identical.

I had copied the wrong formula ... since it was within the 10 minute editing window, I posted the corrected formula to Message #6 instead of repeating here. Thanks for catching that.

 

[mrblister]

Thank you everyone, for the responses!

 

[Aladin Akyurek]

Thank you everyone, for the responses!

Nice, considering post #2 totally misses the * operator in front of J5:O5!...