[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
TRUETYPIST
General User

Joined: 25 Oct 2006
Posts: 47

 Posted: Tue Nov 27, 2007 2:29 am    Post subject: [Solved] Price List - subtotals - Ampersand I need to calculate the subtotals on a price list . This values must show on a certain column i.e: I need to calculate the sum of prices for each item with a code starting with 1, or 2,... On the line "item 1." (major item) I need the sum of all the prices for lines with "item 1.1.", "item 1.20.5",... I've checked: http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_SUMIF_function I found there the general example: SUMIF(B2:B4; "<"&F2; C2:C4) Very Basic question: what is the meaning of the ampersand "&" ? I can't find it on OOo's help When using SUMIF(test_range; condition; sum_range), how can I use "Absolute" and "relative" cell references on the condition ? I can't grasp it.Last edited by TRUETYPIST on Tue Dec 04, 2007 9:08 am; edited 1 time in total
acknak
Moderator

Joined: 13 Aug 2004
Posts: 4295
Location: ~ 40°N,75°W

Posted: Tue Nov 27, 2007 7:13 am    Post subject:

Hmm, you're right. It isn't easy to find.

Oone might guess that it is an operator of some kind, like "+" or "*", in which case, you would look for a table or list of all the operators:

Help > OO.org Help > OOo Calc > Contents
Spreadsheets > Functions Types and Operators > Operators in OOo Calc
 Quote: Text operators The operator combines separate texts into one text. Operator__Name_______________Example & (And)___text concatenation AND__"Sun" & "day" is "Sunday"
TRUETYPIST
General User

Joined: 25 Oct 2006
Posts: 47

 Posted: Wed Nov 28, 2007 3:19 pm    Post subject: To acknak: Thank you for your reply. I got it. I still don't know how to solve my problem
acknak
Moderator

Joined: 13 Aug 2004
Posts: 4295
Location: ~ 40°N,75°W

 Posted: Wed Nov 28, 2007 7:46 pm    Post subject: Maybe this will help: sum_example.ods The sample sheets give an example using SUM in an array formula, like this: {=SUM(IF(ISERR(SEARCH(" 1\.";A2:A7));0;B2:B7))} And (on a separate sheet), using SUMIF, like this: =SUMIF(F2:F7;"=1";B2:B7) The array SUM is slightly more complicated but needs no helper columns; the SUMIF needs three intermediate results in order to get the item number for filtering the rows.
TRUETYPIST
General User

Joined: 25 Oct 2006
Posts: 47

 Posted: Tue Dec 04, 2007 9:08 am    Post subject: Thank you, acknak ! (I took a while to get back and to evaluate you formulas...) I map-googled your coordinates and became very surprised ! I would not imagine that such an expert would live in on the Chinese-Kyrgizstan border !! (in the middle of a desert) Then I realized that 75ºW means "-75"... Philadelphia is a somewhat more reasonable location
acknak
Moderator

Joined: 13 Aug 2004
Posts: 4295
Location: ~ 40°N,75°W

 Posted: Tue Dec 04, 2007 1:17 pm    Post subject: Well, I don't exactly live in a golf course either, but it's just around the corner. If you like virtual travel, you might want to add to this thread: http://www.oooforum.org/forum/viewtopic.phtml?t=51298
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum