https://bugs.documentfoundation.org/show_bug.cgi?id=85539
--- Comment #7 from ady <[email protected]> --- (In reply to DDCorley from comment #0) > =SUMPRODUCT(SUMIF(INDIRECT("$"&$A$1:$A$5&".$A$2:$A$23"),$A19, > INDIRECT("$"&$A$1:$A$5&".D$2:D$23"))) That's from attachment 108545 from comment 0, worksheet named "SUMPRODUCTS", cell C19 > Converting similar function from Excel. Does such equivalent construction actually work in Excel? > The formula should look for sheet names in cells A1 through A5 and look for > a match of the value supplied in A19 on any of the sheets in cells A2:A23, > summing on values in D2:D23. > > A1:A5 - Range on same sheet as formula that contains the names of sheets to > be summed. > A2:A23 - Is the range on the individual sheets to look for the value > supplied in A19. > D2:D23 - Range on the individual sheets with the values to sum if match is > found. > > If the range A1:A5 is replaced with a single cell (A1 through A5), the > formula works and the values are populated. That change, from originally using "A1:A5" to using just one cell (A1) means that the arguments for SUMIF() are no longer 3D ranges, but a range within a specific worksheet. Therefore, that change makes the arguments for SUMIF() receive simple ranges from one worksheet (which SUMIF() accepts), not arrays nor 3D references (which, AFAIR, SUMIF() do not accept). Is SUMIF() capable of having 3D references as its arguments? Is Excel accepting those? >From comment 0, it is clear what was the desired result. But it is not clear to me what is being reported as incorrect or as unexpected behavior (or as RFE). The subject/title of this bug report at the time I am writing says: "SUMPRODUCT not summing multiple sheets". Whichever the problem might be (if there is really a bug, or if there is some RFE), it would be originated before SUMPRODUCT() steps-in, so that's not the issue. To sum-up, it is not clear to me what this report is about. -- You are receiving this mail because: You are the assignee for the bug.
