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

Discuss the spreadsheet application
Post Reply
peltto7
Posts: 4
Joined: Fri Jun 15, 2018 3:38 am

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

Post 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.
Attachments
table.png
table.png (2.5 KiB) Viewed 3057 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
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

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

Post by robleyd »

The SUMIF() function should do what you want. Search sumif in the Help - F1 for more information.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
coray80
Volunteer
Posts: 357
Joined: Thu Mar 01, 2012 6:41 am

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

Post 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
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
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Post 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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
peltto7
Posts: 4
Joined: Fri Jun 15, 2018 3:38 am

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

Post 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.
OpenOffice 4.1.1
Windows 7 64bit
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Post 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)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Post Reply