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.
[Solved] Sum values in a column based on values in another
[Solved] Sum values in a column based on values in another
- Attachments
-
- table.png (2.5 KiB) Viewed 3143 times
Last edited by Hagar Delest on Sun Jan 20, 2019 11:56 am, edited 1 time in total.
Reason: tagged solved
Reason: tagged solved
OpenOffice 4.1.1
Windows 7 64bit
Windows 7 64bit
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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
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!
SUMPRODUCT is a better choice
at least on my old version of AOO
Code: Select all
=SUMIF(C1:C4;"<2000";A1:A4)
Code: Select all
=SUMPRODUCT(C1:C4<2000;A1:A4)
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
If your question has been answered please add [solved] to the title by using the edit button at your first post
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.
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.
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.
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.
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
Windows 7 64bit
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
=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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.