# Formula alternative to Sumproduct

#### Jemma Atkinson

##### Well-known Member
Hi, is there an alternative formula to sumproduct where i can use named ranges? The below wont work for named range

=SUMPRODUCT(--(Over2day!\$Z\$7:\$Z\$10000=Rec_Criteria!C14),--(Over2day!\$H\$7:\$H\$10000>=2),--(Over2day!\$H\$7:\$H\$10000<=29))

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Yard

##### Well-known Member
Hi, what do you mean when you say it "won't work"?

You can use named ranges in SUMPRODUCT. Are you sure the named ranges are all the same size?

#### Jemma Atkinson

##### Well-known Member
I was under the impression that you cant you named ranges in Sumproduct, yeah they are all the same size

Hi, what do you mean when you say it "won't work"?

You can use named ranges in SUMPRODUCT. Are you sure the named ranges are all the same size?

#### Sandeep Warrier

##### Well-known Member
Hi

I'm not sure why your SUMPRODUCT formula doesn't work with named ranges.

However, try

With Control+Shift+Enter and not just Enter

Code:
``=SUM(IF(NamedRange1=Rec_Criteria!C14,IF(NamedRange2>=2,IF(NamedRange3<=29,1,0))))``

##### MrExcel MVP
Hi, is there an alternative formula to sumproduct where i can use named ranges? The below wont work for named range

=SUMPRODUCT(--(Over2day!\$Z\$7:\$Z\$10000=Rec_Criteria!C14),--(Over2day!\$H\$7:\$H\$10000>=2),--(Over2day!\$H\$7:\$H\$10000<=29))

Activate Insert|Name|Define.
Type Size in the Names in workbook box.
Type the following in the Refers to box:

=MATCH(9.99999999999999E+307,Over2day!\$H:\$H)-ROW(Over2day!\$H\$7)+1

Type Hrange in the Names in workbook box.
Type the following in the Refers to box:

=OFFSET(Over2day!\$H\$7,0,0,Size)

Type Zrange in the Names in workbook box.
Type the following in the Refers to box:

=OFFSET(Over2day!\$Z\$7,0,0,Size)

Click OK.

Now you can invoke:

=SUMPRODUCT(--(Zrange=Rec_Criteria!C14),--(Hrange>=2),--(Hrange<=29))

instead of the original SumProduct formula you posted.

Replies
4
Views
45
Replies
3
Views
58
Replies
3
Views
171
Replies
10
Views
581
Replies
1
Views
80

1,127,199
Messages
5,623,321
Members
415,966
Latest member
ctorohuamanchumo

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

### Which adblocker are you using?

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

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