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 (D8:D30) 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.
|
You need SUMIF for D32 and COUNTIFS for D33. The explanations are in Libreoffice help files (or Google).
|
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! |
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. |
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 |
Quote:
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". |
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 |
1 Attachment(s)
Works fine here. Notice that the formula is sumifs().
|
Quote:
Code:
D E 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 |
[SOLVED] LibreOffice Calc - Sum values in one column based on criteria in another
Quote:
Code:
=SUMIFS(D8:D30,E8:E30,">0") Code:
">0" Code:
"<>isblank" Thank you for your help. Michael |
When did they change "=sumif" to "=sumifs"? "=sumif" still works in my Libreoffice 5.3.3.
|
All times are GMT -5. The time now is 06:37 AM. |