Return a SUM from multiple sheets-CodePudding

I have a formula that returns values from multiple sheets under certain criteria and provides the sum.

=SUMPRODUCT(IFERROR(SUMIF(INDIRECT("'"&B17&" "&Year&"*'!C:C"),"Total",INDIRECT("'"&B17&" "&Year&"*'!Z:Z")),0))

I am looking to return a value that is correlated to Total, but since there are multiple Totals on each page I get back the sum of them all. I only need one Total (that is listed as 100%) from each sheet rather than all of them.

CodePudding user response：

Linking spreadsheets is inherently fragile. If you do it, then to minimise fragility:

use named ranges for your target cells.
bring them to your consolidating spreadsheet as single numbers - then sum them there.

CodePudding user response：

=SUMPRODUCT(IFERROR(SUMIFS(INDIRECT("'"&B17&" "&Year&"*'!Z:Z"),INDIRECT("'"&B17&" "&Year&"*'!C:C"),B19,INDIRECT("'"&B17&" "&Year&"*'!AA:AA"),"100%")/COUNTIFS(INDIRECT("'"&B17&" "&Year&"*'!C:C"),B19,INDIRECT("'"&B17&" "&Year&"*'!AA:AA"),"100%"),0))

This is the solution I came up with. Changed to SUMIFS to make criteria more detailed searching for "Total" (which is B19) and "100%". Then divide by COUNTIFS using the same criteria as SUMIFS since each sheet has different amount of Total rows