Summing across multiple tabs and criteria

gstelea

New Member
Joined
Mar 4, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Mar 4, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
That happens;)...thanks for the update.
 

Watch MrExcel Video

Forum statistics

Threads
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.
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