https://bugs.documentfoundation.org/show_bug.cgi?id=168695
Bug ID: 168695
Summary: EDITING: SUMIF and AVERAGEIF ignore additional columns
in multi-column sum_range
Product: LibreOffice
Version: 25.8.1.1 release
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: libreoffice-bugs@lists.freedesktop.org
Reporter: todorov...@yandex.ru
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
--
You are receiving this mail because:
You are the assignee for the bug.
