| Summary: | SUMPRODUCT not summing multiple sheets | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | DDCorley |
| Component: | Spreadsheet | Assignee: | Not Assigned <libreoffice-bugs> |
| Status: | NEW --- | QA Contact: | |
| Severity: | normal | ||
| Priority: | medium | CC: | mariosv |
| Version: | Inherited From OOo | ||
| Hardware: | Other | ||
| OS: | Windows (All) | ||
| Whiteboard: | |||
| i915 platform: | i915 features: | ||
| Attachments: | Sample formulas showing what works, what doesn't and expected results. | ||
Attachment is a .ods file. Hi DDCorley, thanks for reporting. Reproducible. Win7x64Ultimate LibreOffice Version: 4.3.3.1 Build ID: 7d55112667c8fcddb67bc3803796b46c93aa56b0 AOo 4.1.1 Seems inherited from OOo. |
Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.
Created attachment 108545 [details] Sample formulas showing what works, what doesn't and expected results. =SUMPRODUCT(SUMIF(INDIRECT("$"&$A$1:$A$5&".$A$2:$A$23"),$A19,INDIRECT("$"&$A$1:$A$5&".D$2:D$23"))) Converting similar function from 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. Attached is an example file, the different sheets are: INPUT - sheet where values can be entered. Interest 1 thru Interest 5 - the sheets where values are calculated. SUMPRODUCTS - sheet that has example formula. Verify SUMPRODUCTS - Sheet created with work around that has correct values the formula should produce.