-2

I need data to be shown in B2:B100 of a summary page. The data should be pulled from nine other worksheets, to calculate the volume sold from all sheets and then assign it to the corresponding district of the summary page. It should be done once the loan settles and not before.

My understanding is that you would use a SUMIFS formula but I can't seem to get this to work for more than one sheet at a time.

Summary Screen

Data

pnuts
  • 58,317
  • 11
  • 87
  • 139
David
  • 1
  • The [SUMIFS function](https://support.office.com/en-us/article/SUMIFS-function-9DD6179E-CCED-41DD-AC38-08FDF5B929E5) does not work across worksheets. As a rule of thumb, any native worksheet function that provides iterative calculation will not work across multiple worksheets. Stack the SUMIFS functions into a single formula may seem long-winded but for 9 worksheets it is a better solution than an [INDIRECT](https://support.office.com/en-us/article/indirect-function-21f8bcfc-b174-4a50-9dc6-4dfb5b3361cd) array. –  Oct 12 '15 at 23:19
  • Hey Jeeped, Thanks so much for your response and this may seem this a really novice question but how do you "stack the SUMIFS"? – David Oct 12 '15 at 23:24
  • SUMIFS(...)+SUMIFS(...)+SUMIFS(...)+SUMIFS(...)+SUMIFS(...)+SUMIFS(...)+SUMIFS(...)+SUMIFS(...)+SUMIFS(...) –  Oct 12 '15 at 23:29

1 Answers1

1

If a stacked set of SUMIFS functions for your nine worksheets is impractical due to additional worksheets or expanding criteria, then you can create an array of the worksheet names and use the INDIRECT function to reference them in an array formula.

With nine worksheets holding identical layouts like the following:

        enter image description here

On the Summary worksheet, create a list of the nine worksheets. I've used G2:G10. With this in place, an array¹ formula involving both the SUM and SUMIFS functions can use the list to provide the 'start-to-finish' worksheet collection you are looking for.

       enter image description here

The array¹ formula in Summary!C4 is,

=SUM(SUMIFS(INDIRECT($G$2:$G$10&"!B:B"), INDIRECT($G$2:$G$10&"!A:A"), B4))

¹ Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.

Community
  • 1
  • 1