# sumproduct with a named range or lookup

#### jenlj

##### New Member
Hi

I have a large, complicated spreadsheet but have made an easy example for my question!

 Jan Feb March Dog A 5​ 13​ 15​ Cat D 10​ 12​ 12​ Duck B 5​ 11​ 1​ Fox A 12​ 9​ 2​ Dog B 8​ 6​ 4​ Dog A 9​ 4​ 2​ Cat E 6​ 7​ 3​

I am using a sumproduct formula to return the sum of multiple criteria
=SUMPRODUCT((C243:G250),((C243:C250="Dog")+(C243:C250="Fox"))*(D243:D250="A")*(C243:G243="Jan"))

This part of my formula ((C243:C250="Dog")+(C243:C250="Fox")) is always different so rather than have to update the animal (and sometimes it is one animal and sometimes may) each time I would like to put in a lookup to a named range that I can update. Is this possible with sumproduct?

Thanks

J

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### RoryA

##### MrExcel MVP, Moderator
You can do this:

Book1
CDEFG
243JanFebMarch
244DogA51315
245CatD101212
246DuckB5111
247FoxA1292
248DogB864
249DogA942
250CatE673
251
252Dog26
253Fox
Sheet1
Cell Formulas
RangeFormula
F252F252=SUMPRODUCT(E244:G250*ISNUMBER(MATCH(C244:C250,CriteriaList,0))*(D244:D250="A")*(E243:G243="Jan"))

CriteriaList is defined as C252:C253

#### jenlj

##### New Member
Thanks so much, this works perfectly. Quick question though, is there a way that excel will recognize the text in a cell to be a named range so that instead of writing “CriteriaList”, I can reference a cell? I would like to future proof so that I can drag the formula if I have lots of named ranges.
Thanks, J

#### RoryA

##### MrExcel MVP, Moderator
If you mean that the cell will contain the name of a range, then you can use INDIRECT to convert that to an actual range reference, as long as it isn't a dynamic range name.

Replies
6
Views
84
Replies
15
Views
193
Replies
9
Views
468
Replies
3
Views
325
Replies
16
Views
441

1,136,282
Messages
5,674,829
Members
419,529
Latest member
TommasoP11

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