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?