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.

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

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.

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

Works fine here. Notice that the formula is sumifs().

Attached Thumbnails

 

1 members found this post helpful.

Let's try this:

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

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:

In my case, met my need. I imagine would have worked as well.

Thank you for your help.

Michael

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