Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 16 Feb 2018 19:10:13 +0100Sum values in one column based on criteria in anotherhttps://ask.libreoffice.org/en/question/97204/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.Sun, 28 May 2017 15:28:27 +0200https://ask.libreoffice.org/en/question/97204/sum-values-in-one-column-based-on-criteria-in-another/Comment by Sanjay Prasad for <p>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.</p>
https://ask.libreoffice.org/en/question/97204/sum-values-in-one-column-based-on-criteria-in-another/?comment=146560#post-id-146560VBS = VBScript = Visual Basic ScriptFri, 16 Feb 2018 19:10:13 +0100https://ask.libreoffice.org/en/question/97204/sum-values-in-one-column-based-on-criteria-in-another/?comment=146560#post-id-146560Comment by plgeek for <p>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.</p>
https://ask.libreoffice.org/en/question/97204/sum-values-in-one-column-based-on-criteria-in-another/?comment=97257#post-id-97257use VBS to achieve this.Mon, 29 May 2017 12:58:43 +0200https://ask.libreoffice.org/en/question/97204/sum-values-in-one-column-based-on-criteria-in-another/?comment=97257#post-id-97257Comment by barnmichael for <p>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.</p>
https://ask.libreoffice.org/en/question/97204/sum-values-in-one-column-based-on-criteria-in-another/?comment=97260#post-id-97260Please forgive my ignorance, VBS? Vacation Bible School? I don't find any VBS in the Formula Wizard or other calc function. Is this an add-on to LibreOffice or some other Linux program?Mon, 29 May 2017 13:18:15 +0200https://ask.libreoffice.org/en/question/97204/sum-values-in-one-column-based-on-criteria-in-another/?comment=97260#post-id-97260Answer by barnmichael for <p>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.</p>
https://ask.libreoffice.org/en/question/97204/sum-values-in-one-column-based-on-criteria-in-another/?answer=97452#post-id-97452Finally found it. Thanks to another forum, I was pointed to https://help.libreoffice.org/Calc/SUMIFS_function which described the SUMIFS function. The formula I was looking for turned out to be:
<code>
=SUMIFS(D8:D30,E8:E30,">0")
</code>
Hope this helps somebody out with a similar issue.
Michael
Wed, 31 May 2017 17:09:41 +0200https://ask.libreoffice.org/en/question/97204/sum-values-in-one-column-based-on-criteria-in-another/?answer=97452#post-id-97452