Formula alternative to Sumproduct

[Jemma Atkinson]

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))

 

[Yard]

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]

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]

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

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

 

[Aladin Akyurek]

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

Click Add.

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

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

Click Add.

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.