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

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

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.
Attachments
table.png (2.5 KiB) Viewed 744 times
Last edited by Hagar Delest on Sun Jan 20, 2019 11:56 am, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.1
Windows 7 64bit
peltto7

Posts: 4
Joined: Fri Jun 15, 2018 3:38 am

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

The SUMIF() function should do what you want. Search sumif in the Help - F1 for more information.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 3145
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

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

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
OpenOffice 4.1.2 on Windows 7
coray80
Volunteer

Posts: 357
Joined: Thu Mar 01, 2012 6:41 am

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

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.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 5854
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

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.
OpenOffice 4.1.1
Windows 7 64bit
peltto7

Posts: 4
Joined: Fri Jun 15, 2018 3:38 am

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

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)`
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7494
Joined: Sat Nov 08, 2008 8:08 pm

### Who is online

Users browsing this forum: No registered users and 2 guests