SUM a RANGE of sheets using SUMPRODUCT(SUMIF(INDIRECT

[Sydnelson]

Hello guys, first of all sorry for my bad english. I will try to explain what I need in english, hope everyone can understand.

I created a table in excel to calculate the results of board games I play.

For each day played I made a sheet named Jogo1, Jogo2 ... where I type the result of each game. And there is another sheet that count the results of every day played. I would like to SUM a RANGE of days played (sheets), but the formula doesn't work, I had to create a formula by adding the sheets one by one. Check out the two formulas I'm using and working:

=IF(ISBLANK($B7);"";SUMPRODUCT(SUMIF(INDIRECT("'Jogo"&{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30}&"'!$B$7:$B$206");B7;INDIRECT("'Jogo"&{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30}&"'!$H$7:$H$206"))))

=IF(ISBLANK($B7);"";SUM(IF(ISNA(IF(VLOOKUP($B7;Jogo1!$B$7:$K$206;COLUMN(Jogo1!$I$7);FALSE);1;NA()));0;1))+IF(ISNA(IF(VLOOKUP($B7;Jogo2!$B$7:$K$206;COLUMN(Jogo2!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo3!$B$7:$K$206;COLUMN(Jogo3!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo4!$B$7:$K$206;COLUMN(Jogo4!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo5!$B$7:$K$206;COLUMN(Jogo5!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo6!$B$7:$K$206;COLUMN(Jogo6!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo7!$B$7:$K$206;COLUMN(Jogo7!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo8!$B$7:$K$206;COLUMN(Jogo8!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo9!$B$7:$K$206;COLUMN(Jogo9!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo10!$B$7:$K$206;COLUMN(Jogo10!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo11!$B$7:$K$206;COLUMN(Jogo11!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo12!$B$7:$K$206;COLUMN(Jogo12!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo13!$B$7:$K$206;COLUMN(Jogo13!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo14!$B$7:$K$206;COLUMN(Jogo14!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo15!$B$7:$K$206;COLUMN(Jogo15!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo16!$B$7:$K$206;COLUMN(Jogo16!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo17!$B$7:$K$206;COLUMN(Jogo17!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo18!$B$7:$K$206;COLUMN(Jogo18!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo19!$B$7:$K$206;COLUMN(Jogo19!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo20!$B$7:$K$206;COLUMN(Jogo20!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo21!$B$7:$K$206;COLUMN(Jogo21!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo22!$B$7:$K$206;COLUMN(Jogo22!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo23!$B$7:$K$206;COLUMN(Jogo23!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo24!$B$7:$K$206;COLUMN(Jogo24!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo25!$B$7:$K$206;COLUMN(Jogo25!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo26!$B$7:$K$206;COLUMN(Jogo26!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo27!$B$7:$K$206;COLUMN(Jogo27!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo28!$B$7:$K$206;COLUMN(Jogo28!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo29!$B$7:$K$206;COLUMN(Jogo29!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Jogo30!$B$7:$K$206;COLUMN(Jogo30!$I$7);FALSE);1;NA()));0;1)+IF(ISNA(IF(VLOOKUP($B7;Final!$B$14:$B$18;COLUMN(Final!$J$7);FALSE);1;NA()));0;1))

Note that in these formulas excel sum the Jogo1, Jogo2, Jogo3 to Jogo30 spreedsheets, but it needed a formula that sum a RANGE sheets like Jogo1: Jogo9999 or Jogo1: JogoX.

Would anyone have a tip on how to solve this issue?

For those that wants to open the excel file to better understand how this formula should work, You can download it here.

O Arconte 1.2.zip

Shared with Dropbox

www.dropbox.com

The cells that have the formulas I need help with are:
SHEET: Jogadores, COLUMNS: G H J K L

Thanks for help.

 

[pgc01]

Hi
Welcome to the board

Instead of

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30}

try

ROW(INDIRECT("1:"&A1))

wIth A1 equal to 30

 

[Sydnelson]

Hi
Welcome to the board

Instead of

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30}

try

ROW(INDIRECT("1:"&A1))

wIth A1 equal to 30

Thanks for your reply. Can you reproduce full formula? I try to include your suggestion but doesn't work.

 

[Dave Patton]

Book1
	A	B	C	D
1	List of Sheets
2	Jogo1			60
3	Jogo2
3b

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A3&"'!B7:B206"),B7,INDIRECT("'"&A2:A3&"'!H7:H206")))

Put a list of the relevant Sheet names in a range, my example uses just A2:A3
Edit the formula for your area, you seem to use ";" instead of ",".

 

[pgc01]

Thanks for your reply. Can you reproduce full formula? I try to include your suggestion but doesn't work.

Try:

Set A1 to some number, for ex. 5 and try

=IF(ISBLANK($B7);"";SUMPRODUCT(SUMIF(INDIRECT("'Jogo"&ROW(INDIRET("1:"&A1))&"'!$B$7:$B$206");B7;INDIRECT("'Jogo"&ROW(INDIRET("1:"&A1))&"'!$H$7:$H$206"))))

In this case you'd be using {1;2;3;4;5}

 

[Sydnelson]

Book1
	A	B	C	D
1	List of Sheets
2	Jogo1			60
3	Jogo2
3b

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A3&"'!B7:B206"),B7,INDIRECT("'"&A2:A3&"'!H7:H206")))

Put a list of the relevant Sheet names in a range, my example uses just A2:A3
Edit the formula for your area, you seem to use ";" instead of ",".

Hello Dave, this formula works if the sheet exist, but if the sheet not exist yet the formula will return a error.

Example, if I create sheets from Jogo1 until Jogo200 and apply your formula with A2:A201 (where this range of cells contains sheet names) it works OK.
But if I try to include sheets as needed like Jogo1, Jogo2, Jogo3, then next day I create Jogo4, next week I create Jogo5 the formula returns a error.
Is possible create a formula that cover sheets that not existe yet? Somethig like Jogo1:Jogo200, but ignoring all missing sheets that not exist yet in that range.

 

[Sydnelson]

Try:

Set A1 to some number, for ex. 5 and try

=IF(ISBLANK($B7);"";SUMPRODUCT(SUMIF(INDIRECT("'Jogo"&ROW(INDIRET("1:"&A1))&"'!$B$7:$B$206");B7;INDIRECT("'Jogo"&ROW(INDIRET("1:"&A1))&"'!$H$7:$H$206"))))

In this case you'd be using {1;2;3;4;5}

I try that and return a error: #NAME? "Unrecognized text"

 

[pgc01]

Sorry missing a letter (in 2 places). INDIRECT, not INDIRET

 

[Dave Patton]

Book1
	A	B	C	D
1	rSheets
2	Jogo1			90
3	Jogo2			90
4	Jogo3
3b

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A4&"'!B7:B206"),B7,INDIRECT("'"&A2:A4&"'!H7:H206")))
D3	D3	=SUMPRODUCT(SUMIF(INDIRECT("'"&Table1[rSheets]&"'!B7:B206"),B7,INDIRECT("'"&Table1[rSheets]&"'!H7:H206")))

Create a Table see A1:A4 ; this was initially A1:A3.
Add a sheet say Jogo3
Update the table and it becomes A1:A4.