Page 1 of 1

[Solved] Sum values in a column based on values in another

Posted: Thu Dec 06, 2018 8:47 am
by peltto7
I want to sum values in a column based on values on the same row in another column.

The condition is that the value is counted if either
- other cell on the same row has nothing or
- other cell on the same row has value less than 2000

In the screenshot I want values in column A summed on row 1, 2 and 3. Row 4 is not counted, because on C4 there is 2000. The result would be 100 + 200 + 50 = 350.

Re: Summing values in a column based on values in another co

Posted: Thu Dec 06, 2018 9:13 am
by robleyd
The SUMIF() function should do what you want. Search sumif in the Help - F1 for more information.

Re: Summing values in a column based on values in another co

Posted: Thu Dec 06, 2018 11:59 pm
by coray80
SUMIF won't work when C3 is a blank cell. It would require a 0 in there!

Code: Select all

=SUMIF(C1:C4;"<2000";A1:A4)
SUMPRODUCT is a better choice

Code: Select all

=SUMPRODUCT(C1:C4<2000;A1:A4)
at least on my old version of AOO

Re: Summing values in a column based on values in another co

Posted: Fri Dec 07, 2018 5:33 am
by RusselB
Confirmed in OpenOffice 4.1.6, but LibreOffice 6.0.6.2 works with the SUMIF even with the blank rather than a 0 that isn't displayed.
SUMPRODUCT suggestion works in both.

Re: Summing values in a column based on values in another co

Posted: Sun Jan 20, 2019 3:47 am
by peltto7
Yes, SUMIF would not work for me, it's not working with empty cells.

SUMPRODUCT does the trick.

Now I have another question. How do I have 2 conditions in SUMPRODUCT, let's say I want to sum the values when the value in corresponding cell is at least 2000, but less than 4000.

So it's >=2000 and <4000.

Re: Summing values in a column based on values in another co

Posted: Sun Jan 20, 2019 4:12 am
by FJCC
Each condition that contributes to the chain of ANDs can be stated separately.

Code: Select all

=SUMPRODUCT(C1:C5 >=2000; C1:C5 <4000; A1:A5)