Summing across multiple tabs and criteria

[gstelea]

I am attempting to sum across multiple tabs based on multiple criteria but am #REF! Below is my formula:
-SalesGroup is a Defined Name for a list of tabs
-$F$F is the sum range
-$X$11 and $X$1 are the cells referencing the criteria, with column B and P the corresponding columns in the tabs


=SUMPRODUCT(SUMIFS(INDIRECT("'"&SalesGroup&"'!$f:$f"),INDIRECT("'"&SalesGroup&"'!$b:$b"),$X11,INDIRECT("'"&SalesGroup&"'!$p:$p"),$X$1))

 

[KRice]

I don't see any issues with your formula. A small working example is shown here, with sales1, sale2, and sales 3 worksheets having content in columns B and P, and values in column F. Your formula finds the cases where both match criteria are met and then sums the values corresponding to those matches.

Book4
	B				F										P
1	ListForX11				Values										ListForX1
2	alpha				1										one
3	bravo				2										two
4	charlie				3										three
5	delta				4										four
6	echo				5										five
sales1

Book4
	B				F										P
1	ListForX11				Values										ListForX1
2	charlie				1										three
3	delta				2										four
4	echo				3										five
5	alpha				4										one
6	bravo				5										two
sales2

Book4
	B				F										P
1	ListForX11				Values										ListForX1
2	bravo				1										three
3	charlie				2										four
4	delta				3										five
5	echo				4										two
6	alpha				5										one
sales3

Book4
	C	D	E																			X
1	SalesGroup list		matchP-->																			two
2	sales1
3	sales2
4	sales3
5
6
7	7
8
9
10
11			matchB-->																			bravo
Sheet1

Cell Formulas
Range	Formula
C7	C7	=SUMPRODUCT(SUMIFS(INDIRECT("'"&SalesGroup&"'!$f:$f"),INDIRECT("'"&SalesGroup&"'!$b:$b"),$X11,INDIRECT("'"&SalesGroup&"'!$p:$p"),$X$1))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Named Ranges
Name	Refers To	Cells
SalesGroup	=Sheet1!$C$2:$C$4	C7

 

[gstelea]

Thank you. After four painful hours, I realized that I had a tab name in my list I had not created yet.

 

[KRice]

That happens...thanks for the update.