LinuxQuestions.org
Latest LQ Deal: Latest LQ Deals
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 05-28-2017, 11:58 PM   #1
michael.barnes
Member
 
Registered: Jan 2007
Location: Dallas, Oregon
Distribution: Ubuntu; Knoppix; DSL; Raspbian
Posts: 30

Rep: Reputation: 0
LibreOffice Calc - Sum values in one column based on criteria in another


I have a spreadsheet with column A (A8:A30) containing names and column D (D830) containing points. Column E (E8:E30) is annotated with a sequence number when each name is contacted. Not all names will be contacted in a given session. I want the formula to determine if a contact number was placed in column E, then sum the points for each name contacted in cell D32. I also need the total number of contacts (not the sum of the sequence numbers) from column E(E8:E30) counted into cell D33. The end result will give the total number of contacts and the points earned for that session.
 
Old 05-29-2017, 03:24 AM   #2
petelq
Member
 
Registered: Aug 2008
Location: Yorkshire
Distribution: openSUSE(Leap and Tumbleweed) and a regularly changing third
Posts: 539

Rep: Reputation: Disabled
You need SUMIF for D32 and COUNTIFS for D33. The explanations are in Libreoffice help files (or Google).
 
Old 05-29-2017, 11:38 AM   #3
DavidMcCann
LQ Veteran
 
Registered: Jul 2006
Location: London
Distribution: PCLinuxOS, Debian
Posts: 5,774

Rep: Reputation: 2133Reputation: 2133Reputation: 2133Reputation: 2133Reputation: 2133Reputation: 2133Reputation: 2133Reputation: 2133Reputation: 2133Reputation: 2133Reputation: 2133
The help files are a start, but I'd recommend that you get the manual for any program you use regularly:
http://www.libreoffice.org/get-help/documentation/
These are full-size books, and all available for free!
 
Old 05-29-2017, 11:59 PM   #4
michael.barnes
Member
 
Registered: Jan 2007
Location: Dallas, Oregon
Distribution: Ubuntu; Knoppix; DSL; Raspbian
Posts: 30

Original Poster
Rep: Reputation: 0
Sadly, LQ only has one option for "Did you find this post helpful?" There should also be a "NO" option. I have looked up all I can find in the help files and in the documentation for SUMIF. Far from explanatory.

I'm not sure I was clear in my need. For example, in a given session, contact numbers are entered into cells E10, E17, E20, and E25. At this point, the value of those numbers are not relevant, only the fact that numbers have been entered into those cells. Based on those entries, the values of cells D10, D17, D20, and D25 must be summed and the result entered into D32.

So, what is needed is to test for the presence of data in cells E8:E30 and sum the values of corresponding cells D8:D30, placing the total into cell D32.

Please, RTFM responses are not helpful. If you cannot provide an actual solution, please do not waste your and my time.
 
Old 05-30-2017, 01:48 AM   #5
AnanthaP
Member
 
Registered: Jul 2004
Location: Chennai, India
Posts: 944

Rep: Reputation: 216Reputation: 216Reputation: 216
If each person is contacted not more than once per session (but may not be contacted) then countif and sumif as explained earlier will do.

Otherwise you have to explain what you mean by "annotated" and how you are annotating in column E. Does it end up having the number of times the person was contacted ie. is it a multiplication factor/ How are you storing it?

An example will help.

OK
 
Old 05-30-2017, 04:15 PM   #6
norobro
Member
 
Registered: Feb 2006
Distribution: Debian Sid
Posts: 792

Rep: Reputation: 331Reputation: 331Reputation: 331Reputation: 331
Quote:
Originally Posted by michael.barnes View Post
Sadly, LQ only has one option for "Did you find this post helpful?" There should also be a "NO" option.
We, or at least I could say the same about your post.

Wasn't sure how to do this myself so I decided to RTFM as advised above. Guess what? I found an example that, if I understand your requirements, does exactly what you want. https://help.libreoffice.org/Calc/SUMIFS_function

All you need to do is change criterion1 to "<>0" or "<>isblank".
 
1 members found this post helpful.
Old 05-30-2017, 06:38 PM   #7
AnanthaP
Member
 
Registered: Jul 2004
Location: Chennai, India
Posts: 944

Rep: Reputation: 216Reputation: 216Reputation: 216
Unfortunately, the condition argument in sumif doesn't seem to take non numeric values (isblank() for instance). So you have to extend into column F which will have in F8 =if(isblank(e8),0,d8) and so on. Then in D32, do a straight sum from Column F.

Incidentally even ms excel the big daddy doesn't take non numeric arguments.

OK
 
Old 05-30-2017, 07:21 PM   #8
norobro
Member
 
Registered: Feb 2006
Distribution: Debian Sid
Posts: 792

Rep: Reputation: 331Reputation: 331Reputation: 331Reputation: 331
Works fine here. Notice that the formula is sumifs().
Attached Thumbnails
Click image for larger version

Name:	sumifs.jpg
Views:	79
Size:	12.0 KB
ID:	25145  
 
1 members found this post helpful.
Old 05-31-2017, 02:01 AM   #9
michael.barnes
Member
 
Registered: Jan 2007
Location: Dallas, Oregon
Distribution: Ubuntu; Knoppix; DSL; Raspbian
Posts: 30

Original Poster
Rep: Reputation: 0
Quote:
if each person is contacted not more than once per session (but may not be contacted) then countif and sumif as explained earlier will do.

Otherwise you have to explain what you mean by "annotated" and how you are annotating in column e. Does it end up having the number of times the person was contacted ie. Is it a multiplication factor/ how are you storing it?

An example will help.

Ok
Let's try this:

Code:
      D         E
8     1,701	
9     89	
10    1,466    	1
11    0	
12    506	
13    2,112	
14    2,112	
15    1,155	5
16    1,155	
17    1,762	
18    240	3
19    181	
20    1,663	
21    1,607	2
22    1,607	
23    155	
24    1,011	
25    1,595	4
26    105

TOTAL POINTS:   6,063
TOTAL CONTACTS:   5
Here is a portion of the spreadsheet. During the session, some users were contacted and the order in which they were contacted is entered in column E. The total number of contacts is tallied and entered in D33. That part is easy using =COUNT. Now, the hard part. The points for each user contacted must be added up and the sum entered into D32. Somehow, I need to do something to the effect of "IF E(x) >0 THEN SUM D(x)" Using SUMIF, I can get it to take the cells in column E and add them together if they are >0, but I am not seeing how to test column E for >0 then use the value in column D to add to the total. I have read and reread everything on SUMIF and SUMIFS and am simply not seeing how to do this.

There is a copy of the spreadsheet for each session. During the session, users announce themselves and their current point value, which rarely changes. The operator notes each user in the order in which they announce and, if necessary, updates their points and enters their sequence number. After all users have announced, the operator then goes though the list in order and acknowledges each user. At the end of the session, the tally must show the total number of users contacted and the total points accumulated. Only the points for the users contacted are tallied.

In the example above, users 10, 15, 18, 21, and 25 were contacted. Their points, respectively, are 1466, 1155, 240, 1607, and 1595. These points add up to 6063 points for this particular session with 5 users contacted.

This is a representation of the spreadsheet in use. In the actual sheet, the user list is much larger. I hope this more clearly explains what I am trying to accomplish.

Thanks for any help.

Michael
 
Old 05-31-2017, 02:22 AM   #10
michael.barnes
Member
 
Registered: Jan 2007
Location: Dallas, Oregon
Distribution: Ubuntu; Knoppix; DSL; Raspbian
Posts: 30

Original Poster
Rep: Reputation: 0
[SOLVED] LibreOffice Calc - Sum values in one column based on criteria in another

Quote:
Originally Posted by norobro View Post
Wasn't sure how to do this myself so I decided to RTFM as advised above. Guess what? I found an example that, if I understand your requirements, does exactly what you want. https://help.libreoffice.org/Calc/SUMIFS_function

All you need to do is change criterion1 to "<>0" or "<>isblank".
Well, sir, you win the kewpie doll. Not sure why, but in my previous search, I did not find the page you referenced. That particular page provided the desired result. The formula that met my need is:

Code:
=SUMIFS(D8:D30,E8:E30,">0")
In my case,
Code:
 ">0"
met my need. I imagine
Code:
 "<>isblank"
would have worked as well.

Thank you for your help.

Michael
 
Old 05-31-2017, 05:01 PM   #11
petelq
Member
 
Registered: Aug 2008
Location: Yorkshire
Distribution: openSUSE(Leap and Tumbleweed) and a regularly changing third
Posts: 539

Rep: Reputation: Disabled
When did they change "=sumif" to "=sumifs"? "=sumif" still works in my Libreoffice 5.3.3.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Absolute value of a column in Libreoffice calc Adwat Linux - Software 4 03-10-2015 05:35 AM
Help with Perl to count number of matches in one column based on values in another captainentropy Programming 16 12-19-2013 02:12 PM
[SOLVED] sum a value of column based on the value of other columns umix Linux - Newbie 5 04-11-2013 03:15 PM
[SOLVED] How to sum only specific column values in a row using awk? jv61 Linux - Newbie 3 01-17-2013 11:41 AM
[SOLVED] Delete rows based on values in a column using sed captainentropy Linux - Newbie 6 01-19-2011 08:59 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 08:30 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration