Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenThu, 02 Oct 2014 04:04:54 +0200SUMIF() only first col of range is addedhttps://ask.libreoffice.org/en/question/40569/sumif-only-first-col-of-range-is-added/Libre Office 4.1.5.3 English
Mac OS X 10.9.5 English
equation: =sumif(D6:D500,1,B6:C500)
Popup Definition: SUMIF(range,criteria,sum_range)
purpose: two columns of expense values (e.g. cash in col B, check in col C)
and several columns of expense type with value 1 if expense is that type (food col D, restaurant col E, misc col F)
so if a 1 in the food type column D, then include any value in column B and column C of that row into the total sum.
Only the values in the first column of the sum_range will be added.
Is this a defect or is "sum_range" actually "sum_ROWrange?Thu, 02 Oct 2014 01:03:22 +0200https://ask.libreoffice.org/en/question/40569/sumif-only-first-col-of-range-is-added/Answer by Rugslug for <p>Libre Office 4.1.5.3 English
Mac OS X 10.9.5 English</p>
<p>equation: =sumif(D6:D500,1,B6:C500)</p>
<p>Popup Definition: SUMIF(range,criteria,sum_range)</p>
<p>purpose: two columns of expense values (e.g. cash in col B, check in col C)
and several columns of expense type with value 1 if expense is that type (food col D, restaurant col E, misc col F)
so if a 1 in the food type column D, then include any value in column B and column C of that row into the total sum.</p>
<p>Only the values in the first column of the sum_range will be added.</p>
<p>Is this a defect or is "sum_range" actually "sum_ROWrange?</p>
https://ask.libreoffice.org/en/question/40569/sumif-only-first-col-of-range-is-added/?answer=40574#post-id-40574It's not a defect:
[Click here for MS Excel Hints on SUMIF](http://office.microsoft.com/en-ca/excel-help/sumif-function-HP010062465.aspx)
Says:
>The sum_range argument does not have to be the same size and shape as the range argument. The actual cells that are added are determined by using the upper leftmost cell in the sum_range argument as the beginning cell, and then including cells that correspond in size and shape to the range argument.
You could add two equations of SUMIF:
=SUMIF(D6:D500,1,B6:B500)+SUMIF(D6:D500,1,C6:C500)
Or try SUMPRODUCT:
=SUMPRODUCT((D6:D500)*(B6:C500))
Thu, 02 Oct 2014 04:04:54 +0200https://ask.libreoffice.org/en/question/40569/sumif-only-first-col-of-range-is-added/?answer=40574#post-id-40574