Summing values in a column based on values in another column

Discuss the spreadsheet application

Summing values in a column based on values in another column

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 131 times
OpenOffice 4.1.1
Windows 7 64bit
peltto7
 
Posts: 3
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 4.1.6 - 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: 2264
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: 352
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.
RusselB
Volunteer
 
Posts: 4724
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: No registered users and 13 guests