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

Discuss the spreadsheet application

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

Postby peltto7 » Thu Dec 06, 2018 8:47 am

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
table.png (2.5 KiB) Viewed 407 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

Postby robleyd » Thu Dec 06, 2018 9:13 am

The SUMIF() function should do what you want. Search sumif in the Help - F1 for more information.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2808
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

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

Postby coray80 » Thu Dec 06, 2018 11:59 pm

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
If your question has been answered please add [solved] to the title by using the edit button at your first post
coray80
Volunteer
 
Posts: 356
Joined: Thu Mar 01, 2012 6:41 am

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

Postby RusselB » Fri Dec 07, 2018 5:33 am

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.6 and LibreOffice 6.0.6.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.
User avatar
RusselB
Moderator
 
Posts: 5170
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Postby peltto7 » Sun Jan 20, 2019 3:47 am

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

Postby FJCC » Sun Jan 20, 2019 4:12 am

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
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7190
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests