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 Expand viewCollapse view
`=SUMIF(C1:C4;"<2000";A1:A4)`

SUMPRODUCT is a better choice

- Code: Select all Expand viewCollapse view
`=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 Expand viewCollapse view
`=SUMPRODUCT(C1:C5 >=2000; C1:C5 <4000; A1:A5)`