# Summing across multiple tabs and criteria

#### gstelea

##### New Member
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))

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### KRice

##### Well-known Member
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
BFP
1ListForX11ValuesListForX1
2alpha1one
3bravo2two
4charlie3three
5delta4four
6echo5five
sales1

Book4
BFP
1ListForX11ValuesListForX1
2charlie1three
3delta2four
4echo3five
5alpha4one
6bravo5two
sales2

Book4
BFP
1ListForX11ValuesListForX1
2bravo1three
3charlie2four
4delta3five
5echo4two
6alpha5one
sales3

Book4
CDEX
1SalesGroup listmatchP-->two
2sales1
3sales2
4sales3
5
6
77
8
9
10
11matchB-->bravo
Sheet1
Cell Formulas
RangeFormula
C7C7=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
NameRefers ToCells
SalesGroup=Sheet1!\$C\$2:\$C\$4C7

#### gstelea

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

#### KRice

##### Well-known Member
That happens...thanks for the update.

Replies
3
Views
206
Replies
1
Views
57
Replies
1
Views
147
Replies
12
Views
183
Replies
5
Views
157

1,130,009
Messages
5,639,532
Members
417,095
Latest member
danager2020

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