sumproduct with a named range or lookup

jenlj

New Member
Joined
Jan 23, 2020
Messages
2
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi

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

JanFebMarch
DogA
5​
13​
15​
CatD
10​
12​
12​
DuckB
5​
11​
1​
FoxA
12​
9​
2​
DogB
8​
6​
4​
DogA
9​
4​
2​
CatE
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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,706
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
Joined
Jan 23, 2020
Messages
2
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
May 2, 2008
Messages
36,706
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,774
Messages
5,638,268
Members
417,019
Latest member
PKDP

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

Which adblocker are you using?

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
Top