Multisheet column reference doesn't work for SUMIF or SUMPRODUCT

[sirhc]

I have many sheets all with the same format and I want to conditionally sum certain columns that appear on each sheet.

The SUM function works fine in the following format and will sum A1:A10 across all sheets from sheetA to sheetZ.

=SUM(sheetA.A1:sheetZ.A10)

but the following all return Err:504

=SUMIF(sheetA.B1:sheetZ.B10,"=1",sheetA.A1:sheetZ.A10)
=SUMPRODUCT(sheetA.A1:sheetZ.A10,sheetA.B1:sheetZ.B10=1)
=SUMPRODUCT(sheetA.A1:A10:sheetZ.A1:A10,sheetA.B1:B10:sheetZ.B1:B10=1)

Both should return the sum of A1:A10 on each sheet when the value in the corresponding B cell on each sheet is equal to 1. They do both work if I’m only referencing one other sheet. Like =SUMPRODUCT(sheetB.A1:A10, sheetB.B1:B10=1)

Is this a bug? Is there a workaround?

[Lupp]

What you try to reference (like sheetA.B1:sheetZ.B10) spanning more than one sheet is called a cuboid in the specifying documents (‘OpenFormula’). Only very few functions accept cuboids passed to them by a reference in a parameter position. All these functions are specified to convert the entirety of values contained in the cuboid into a list. The functions doing so are all “accumulating”: SUM, PRODUCT, AVERAGE (2 accumulators - count and sum - in the last case).

What you obviously expect is a handling of cuboids as if they are 3D-arrays. They aren’t.

Quoting @sirhc: “Is this a bug? Is there a workaround?”

It’s not a bug. The relevant workarounds are two:
-1- (Preferred by most experienced users) Don’t create spreadsheet documents with many equally structured sheets. It’s supposed to be bad design.
-2- Prepare the multi-sheet evaluation by the appropriate partial evaluations per sheet.

A solution feasible but coming with severe disadvantages is to write a user function converting cuboids in standardised (m x 1 best) arrays. To such arrays you can then apply standard functions like SUMIF() e.g.