OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[Solved] Price List - subtotals - Ampersand

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
TRUETYPIST
General User
General User


Joined: 25 Oct 2006
Posts: 47

PostPosted: Tue Nov 27, 2007 2:29 am    Post subject: [Solved] Price List - subtotals - Ampersand Reply with quote

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
Back to top
View user's profile Send private message
acknak
Moderator
Moderator


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

PostPosted: Tue Nov 27, 2007 7:13 am    Post subject: Reply with quote

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"
Back to top
View user's profile Send private message
TRUETYPIST
General User
General User


Joined: 25 Oct 2006
Posts: 47

PostPosted: Wed Nov 28, 2007 3:19 pm    Post subject: Reply with quote

To acknak:

Thank you for your reply. I got it.

I still don't know how to solve my problem Sad
Back to top
View user's profile Send private message
acknak
Moderator
Moderator


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

PostPosted: Wed Nov 28, 2007 7:46 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
TRUETYPIST
General User
General User


Joined: 25 Oct 2006
Posts: 47

PostPosted: Tue Dec 04, 2007 9:08 am    Post subject: Reply with quote

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 Smile
Back to top
View user's profile Send private message
acknak
Moderator
Moderator


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

PostPosted: Tue Dec 04, 2007 1:17 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group