sumproduct with a named range or lookup

[jenlj]

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

 

[RoryA]

You can do this:

Book1
	C	D	E	F	G
243			Jan	Feb	March
244	Dog	A	5	13	15
245	Cat	D	10	12	12
246	Duck	B	5	11	1
247	Fox	A	12	9	2
248	Dog	B	8	6	4
249	Dog	A	9	4	2
250	Cat	E	6	7	3
251
252	Dog			26
253	Fox
Sheet1

Cell Formulas
Range	Formula
F252	F252	=SUMPRODUCT(E244:G250*ISNUMBER(MATCH(C244:C250,CriteriaList,0))*(D244:D250="A")*(E243:G243="Jan"))

CriteriaList is defined as C252:C253

 

[jenlj]

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]

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.