=sumifs across multiple sheet

[Tybudd]

I can get the formula to work from 1 other sheet, but when I attempt to select multiple sheets, I get a #value error, how am I attempting to select multiple sheets? It looks like this

=SUMIFS('01-03-17:02-28-17'!V:V,'01-03-17:02-28-17'!T:T,F4,'01-03-17:02-28-17'!U:U,"cases")

each tab is named by date. I'm really hoping I can get some help from you guys, got a deadline that I can't complete w/o this formulation.

 

[Aladin Akyurek]

=SUMPRODUCT(SUMIFS(INDIRECT("'"&$A$2:$A$6&"'!V:V"),INDIRECT("'"&$A$2:$A$6&"'!T:T"),F4,INDIRECT("'"&$A$2:$A$6&"'!U:U"),"cases"))

where A2:A6 houses the relevant sheet names like 01-03-17,02-28-17, etc. one by one.

 

[Tybudd]

=SUMPRODUCT(SUMIFS(INDIRECT("'"&$A$2:$A$6&"'!V:V"),INDIRECT("'"&$A$2:$A$6&"'!T:T"),F4,INDIRECT("'"&$A$2:$A$6&"'!U:U"),"cases"))

where A2:A6 houses the relevant sheet names like 01-03-17,02-28-17, etc. one by one.

I might be understanding 100% my apologies.


replacing the $A$2:$A6 with the pages, that would be leaving the quotation marks and the "&" signs as well?

=SUMPRODUCT(SUMIFS(INDIRECT("'"&'02-27-17'!,'02-28-17'!&"'!V:V"),INDIRECT("'"&'02-27-17'!,'02-28-17'!&"'!T:T"),F4,INDIRECT("'"&'02-27-17'!,'02-28-17'!&"'!U:U"),"cases"))

It seems that I'm breaking the formula, either there is something that I'm not putting in correctly, or something I'm taking out, but excel can't read it.

 

[Aladin Akyurek]

Enter the sheet names one by one in the A range with a single quote in front...

A2: '02-27-17
A3: '02-28-17

and so on in a sheet where you have a criterion in F4.


And in a convenient cell in this sheet, in G4 for example, enter the formula:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&$A$2:$A$6&"'!V:V"),INDIRECT("'"&$A$2:$A$6&"'!T:T"),F4,INDIRECT("'"&$A$2:$A$6&"'!U:U"),"cases"))

Adjust this for the A range.

Note that you are not entering "pages" or sheet names in this formula, just the range which houses the sheet names.

 

[Tybudd]

Enter the sheet names one by one in the A range with a single quote in front...

A2: '02-27-17
A3: '02-28-17

and so on in a sheet where you have a criterion in F4.


And in a convenient cell in this sheet, in G4 for example, enter the formula:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&$A$2:$A$6&"'!V:V"),INDIRECT("'"&$A$2:$A$6&"'!T:T"),F4,INDIRECT("'"&$A$2:$A$6&"'!U:U"),"cases"))

Adjust this for the A range.

Note that you are not entering "pages" or sheet names in this formula, just the range which houses the sheet names.

Wow Aladin, thanks that was a more clear explanation for me.

I have another question since the naming has to go by cell instead of in the actual formula.

Do you know of a script, that will look up the names of each worksheet in the workbook, and print? So I don't have to manually input each tab?

 

[Aladin Akyurek]

Wow Aladin, thanks that was a more clear explanation for me.

I have another question since the naming has to go by cell instead of in the actual formula.

Do you know of a script, that will look up the names of each worksheet in the workbook, and print? So I don't have to manually input each tab?

Define slist using Formulas | Name Manager as referring to:

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")


In A2 of the formula sheet enter and copy down:

=IFERROR(INDEX(SheetList,ROWS($A$2:A2)),"")


Adust if necessary the A range the foregoing formula creates and use that range in the SUMPRODUCT formula in lieu of A2:A6.