SUMIF, INDIRECT, SUMPRODUCT

[vzq032372]

I have a spreadsheet with multiple sheets that look exactly the same. I have found a way to sum all of them together but now I'm being asked to sum up only the tabs that the user selects.

I am trying to figure it out and so far only have the following (see pic attached). I'm trying to update the second formula so that it only includes the tabs marked with a 1 next to it. And I'm only adding the "1" as some sort of way to define the tabs they want to sum up. Let me know I need to clarify and thanks in advance for your help.

 

[jasonb75]

Not retyping your whole formula, here's the basic format of what you need.

=SUM(IF(List!H3:H8=1,INDIRECT(...),0))

 

[JEC]

If you have office 365

=SUM(INDIRECT("'"&FILTER(A1:A4,B1:B4=1)&"'!A1"))

 

[vzq032372]

Not retyping your whole formula, here's the basic format of what you need.

=SUM(IF(List!H3:H8=1,INDIRECT(...),0))

This formula returns #VALUE when I try it

 

[jasonb75]

This formula returns #VALUE when I try it

It's not a complete formula, it's just to demonstrate the basic structure of what is required. As I said, I'm not retyping the whole thing. We can't copy and paste a formula from a screen capture.

 

[Dave Patton]

Review the syntax in my example.

3D Sumif.xlsm
	A	B	C	D	E	F	G
1	rSheets	Required
2	1a	1				15	18
3	1b					45	60
4	1c	1
5	1d	1
Total

Cell Formulas
Range	Formula
F2:G3	F2	=SUM(INDIRECT("'"&FILTER($A$1:$A$5,$B$1:$B$5=1)&"'!"&CELL("address",C5)))

Named Ranges
Name	Refers To	Cells
rSheets	=OFFSET(Total!$A$2,0,0,COUNTA(Total!$A$2:$A$15),1)	F2:G3

 

[Dave Patton]

N.B.
For your question, you do not need the Named Range that showed on the post.
Just look at the data in A1:B5
Named RangesNameRefers ToCellsrSheets=OFFSET(Total!$A$2,0,0,COUNTA(Total!$A$2:$A$15),1)F2:G3