Description: The sheet is from the author's solution to problem 9 in Chapter 21 of the book "Microsoft Excel 2016 Data Analysis and Business Modeling". I get the right anwser in Excel, but the wrong result when using Calc. There is a problem with SUMIF. It is summing the cell above the the proper match in each case. See explanation below and attached sheet with the behavior. Steps to Reproduce: 1.Set Product value of C21 to "plane" 2.Set Month value of D21 to "Jan" 3. cell E21 should show result of adding C9+C13, which is 160. Excel gives right answer. Calc gives result of adding C8+C12 = 266. Actual Results: See above "Steps to Reproduce". Calc gives result of adding C8+C12 = 266. Excel gives right (C9+C13) result. Expected Results: cell E21 should show result of adding C9+C13, which is 160. Excel gives right answer. Reproducible: Always User Profile Reset: No Additional Info: The sheet is from the author's solution to problem 9 in Chapter 21 of the book "Microsoft Excel 2016 Data Analysis and Business Modeling".
Created attachment 143682 [details] Sheet that exhibits the problem with SUMIF in Calc. Works in Excel.
In E21, the formula (=SUMIF(B8:B16;C21;OFFSET(B7;0;MATCH(D21;C7:I7;0);9;1))) adds values from a columnar reference: > OFFSET(B7;0;MATCH(D21;C7:I7;0);9;1) This reference gives a 9-row 1-column range starting from column which offset is calculated using MATCH - that's OK; and from row 7(!), since it offsets 0 rows from the reference cell C7. So, when SUMIF finds the indices of the matching cells in its condition column (B8:B16), say, it be second and sixth elements for "plane", it takes second and sixth elements from C7:C15. They are, of course, not what you'd expect, because you forgot to use 1 as the second argument to OFFSET. Closing NOTABUG. Please feel free to reopen if you disagree; please describe why you do that in that case.
And Excel 2013 gives the same 266 for those values in C21 and D21 in the attachment 143682 [details].
... And of course, testing both this and the S22_9.xlsx (Chapter 22 problem 9 solution) from the Download Part 1 from https://www.microsoftpressstore.com/store/microsoft-excel-data-analysis-and-business-modeling-9781509304219#downloads using Excel 2016 also gives 266 instead of 170, as expected. That only it is the author's solution doesn't make it correct.