Ask Your Question
1

Multisheet column reference doesn't work for SUMIF or SUMPRODUCT

asked 2017-10-11 04:22:49 +0200

sirhc gravatar image

updated 2017-10-12 18:19:53 +0200

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2017-10-12 21:20:57 +0200

Lupp gravatar image

updated 2017-10-12 23:26:35 +0200

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-10-11 04:22:49 +0200

Seen: 24 times

Last updated: Oct 12