Description: When attempting to calculate an average or sum over a data range consisting of two columns, with a condition checked against a third column, the average is calculated only from the first column / the sum is computed only from the first column. Data from the second column is ignored. Example: =SUMIF($B$2:$B$1000,"=7",C2:D1000)/COUNTIF($B$2:$B$1000,"=7") I get the average for column "C" only; column "D" is not included. The same issue occurs when using the AVERAGEIF function. Steps to Reproduce: Open a new Calc spreadsheet. Enter sample data into columns A, B, C, and D: In column A (e.g., A2:A5), enter names or any text (e.g., "Student1", "Student2", "Student3", "Student4"). In column B (e.g., B2:B5), enter school numbers (e.g., "7", "8", "7", "9"). In column C (e.g., C2:C5), enter Geography scores (e.g., "60", "70", "65", "80"). In column D (e.g., D2:D5), enter Informatics scores (e.g., "70", "80", "75", "90"). In an empty cell (e.g., F1), enter the formula: =SUMIF($B$2:$B$5,"=7",C2:D5)/COUNTIF($B$2:$B$5,"=7") and press Enter. Actual Results: The formula returns the value 62.5. This result is calculated as (60 + 65) / 2, which is the average of only the values in column C (Geography) for rows where column B equals "7". The values in column D (Informatics) are completely ignored in the calculation. Expected Results: The formula should return the value 130. This result should be calculated as (60 + 70 + 65 + 75) / 2 = 270 / 2 = 135. The SUMIF function should sum all corresponding cells in the multi-column range C2:D5 (i.e., both Geography and Informatics scores) for the matching rows, and then divide by the count of matching rows. The expected behavior is that SUMIF processes the entire specified sum_range (C2:D5), not just its first column. Reproducible: Always User Profile Reset: No Additional Info: Version: 25.2.5.2 (X86_64) / LibreOffice Community '''the same in version 25.8.1''' Build ID: 03d19516eb2e1dd5d4ccd751a0d6f35f35e08022 CPU threads: 28; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Vulkan; VCL: win Locale: ru-RU (ru_RU); UI: ru-RU Calc: CL threaded
What you describe is exactly what is is intended to do. This is explained in the documentation for e.g. SUMIF [1] : > If the optional range S is included, then the values of S starting from the top > left cell and matching the geometry of R (same number of rows and columns) are > summed if the corresponding value in R meets the Criterion. In your example, D2 would only be summed, if you used B2:C1000 in the first argument, and C2 would happen to match the criterion. The functions do *not* consider a single match in B2 as validating the "whole row" in the third argument; only one element in third argument matched one element in first argument. Not a bug. [1] https://docs.oasis-open.org/office/OpenDocument/v1.4/OpenDocument-v1.4-part4-formula.html#__RefHeading__1018584_715980110