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

[Hagar Delest]

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.

[robleyd]

The SUMIF() function should do what you want. Search sumif in the Help - F1 for more information.

[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

[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.

[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.

[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)