Sumifs with nested Xlookup-- Adding Indirect to perform calculation across multiple sheets

[RR92]

Hello All,

I routinely use a sumifs formula with a nested Xlookup to return the column of values I want to sum up: =SUMIFS(XLOOKUP(J$8,'Sheet1'!$C$7:$AN$7,'Sheet1'!$C$8:$AN$220,,0),'Sheet1'!$A$8:$A$220,'Lookup'!$I11)

The formula above works fine. However, when I try to nest the Indirect function to perform the same calculation across multiple worksheets, I get an N/A error (Sheets is a named range of tab names): =SUMIFS(XLOOKUP(J$8,INDIRECT("'"&Sheets&"'!"&"C7:AN7"),INDIRECT("'"&Sheets&"'!"&"C8:AN220"),,0),INDIRECT("'"&Sheets&"'!"&"A8:A220"),'Lookup'!$I29)

When I add the indirect formula to a more basic Sumifs (without the xlookup) formula, it always works. But for some reason I cannot get the Indirect formula to add up the first calculation above across worksheets. Is there something that would prevent the Indirect function from working across sheets with a 3D lookup formula?

 

[Eric W]

Welcome to the MrExcel forum!

3-D formulas are dicey at the best of times. Even in the best of circumstances, you have to get them exactly right, and sometimes there's just no way to do it. In this case, we can, but only if the heading columns on each sheet (C7:AN7) have the headings in the same order on each sheet. Then we can look up the column on the first sheet, then make an INDIRECT string that we can use for all sheets. So if your sheets look like this:

Book4
	A	B	C	D	E	F
7			Alpha	Beta	Gamma	Delta
8	a		1	2	3	4
9	b		2	4	6	8
10	c		3	6	9	12
11	a		4	8	12	16
12	b		5	10	15	20
13	c		6	12	18	24
14	a		7	14	21	28
15	b		8	16	24	32
16	c		9	18	27	36
17	d		10	20	30	40
18	d		11	22	33	44
19	d		12	24	36	48
20
Sheet1

Book4
	A	B	C	D	E	F
7			Alpha	Beta	Gamma	Delta
8	a		100	200	300	400
9	b		200	400	600	800
10	c		300	600	900	1200
11	a		400	800	1200	1600
12	b		500	1000	1500	2000
13	c		600	1200	1800	2400
14	a		700	1400	2100	2800
15	b		800	1600	2400	3200
16	c		900	1800	2700	3600
17	d		1000	2000	3000	4000
18	d		1100	2200	3300	4400
19	d		1200	2400	3600	4800
20
Sheet2

You can do this:

Book4
	H	I	J	K
7			Heading
8			Beta
9
10	Sheets	lookup value	1 sheet result	Multi-Sheet result
11	Sheet1	a	24
12	Sheet2		24	2424
Lookup

Cell Formulas
Range	Formula
J11	J11	=SUMIFS(XLOOKUP(J$8,Sheet1!$C$7:$AN$7,Sheet1!$C$8:$AN$220,,0),Sheet1!$A$8:$A$220,Lookup!$I11)
J12	J12	=SUMIFS(INDEX(Sheet1!C8:AN220,0,MATCH(J8,Sheet1!C7:AN7,0)),Sheet1!A8:A220,I11)
K12	K12	=LET(ad,"!"&TEXTJOIN(":",,ADDRESS({8,220},COLUMN(C7)+MATCH(J8,Sheet1!C7:AN7,0)-1)),SUM(SUMIFS(INDIRECT(Sheets&ad),INDIRECT(Sheets&"!a8:a220"),I11)))

Named Ranges
Name	Refers To	Cells
Sheets	=Lookup!$H$11:$H$12	K12

The J12 formula is a shorter version of your J11 formula. The K12 formula looks up the proper column, performs the SUMIFS on all sheets, sums those amounts, and shows the total. I tried to play with moving the column headings around, but I think that's just too much for Excel to keep track of internally.

Hope this helps!