# Can you help me shorten this formula?

#### mrblister

##### Board Regular
=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?

### Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### Aladin Akyurek

##### MrExcel MVP
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

##### MrExcel MVP
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

##### MrExcel MVP
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

##### MrExcel MVP
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

##### MrExcel MVP
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)

Last edited:

#### Marcelo Branco

##### MrExcel MVP
Rick

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

M.

#### Rick Rothstein

##### MrExcel MVP
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.

Last edited:

#### mrblister

##### Board Regular
Thank you everyone, for the responses!

#### Aladin Akyurek

##### MrExcel MVP
Thank you everyone, for the responses!

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

Replies
6
Views
221
Replies
2
Views
117
Replies
0
Views
249
Replies
0
Views
130
Replies
21
Views
700

Threads
1,190,809
Messages
5,983,040
Members
439,815
Latest member
yoswosz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back