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

Author Message
Thunderace
General User

Joined: 27 Jan 2007
Posts: 5

 Posted: Sat Jan 27, 2007 6:52 am    Post subject: SUM a column of figures IF the column next to it has X text Hi all I'm new here and thanks to the developers for this great software. Onto my n00b problem. I'm new to using calc and am in the process of creating an automated accounts package for myself that draws its data from my osCommerce webshop. What I'm trying to do: - Lets say I have column I4 to I999 as a row of financial cells Next to it I have column H4 to H999 as text based cells What I want to do is SUM(I4:I999) ONLY WHERE the H column="thistext" and put the necessary code to calculate this into a cell. Any and all help gratefully received
JohnV

Joined: 07 Mar 2003
Posts: 9127
Location: Lexinton, Kentucky, USA

 Posted: Sat Jan 27, 2007 7:08 am    Post subject: You want the SUMIF function. =SUMIF(H4:H999;"=thistext";I4:I999) This function supports regular expression so "=this.*" would also work for "this" followed by anything or nothing. Regular expressions are enabled at Tools > Options > Calc > Calculate and I''m not sure what the default setting is.
Thunderace
General User

Joined: 27 Jan 2007
Posts: 5

 Posted: Sat Jan 27, 2007 7:15 am    Post subject: Well I'l be !##!@ I tried SUMIF too! obviously wrongly. Works a treat, thanks v much Johnv PS Can't you just change the whole system to PHP just for me So i don't have to learn a whole new language
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10081
Location: Germany

 Posted: Sat Jan 27, 2007 1:24 pm    Post subject: I don't know php, but "spreadsheet language" is very simple: Types: 1. floating point numbers (double) All date/times, currencies, booleans are just formatted numbers. See Tools>Options>Calc>Calculation for date-options. =N(Number) returnes the real value of a formatted number. 2. strings Wrap hard coded strings in doublequotes. 3. errors (only returned by formulas) Cell-references like \$Sheet1.\$A\$1 are pointers to other cell-values Range-references like \$Sheet1.\$A\$1:\$B\$2 are pointers to arrays of cell-values. Relative/absolute/mixed references, of a formula in cell Sheet1.B4: =A1 points to the cell 3 rows above, one column left on this sheet =\$A1 points to the cell 3 rows above in first column =A\$1 points to the cell in row 1, one column left =\$A\$1 points to A1 actually =\$Sheet2.\$A\$1 points to A1 on Sheet2 =Sheet2.\$A\$1 points to A1 on next sheet (assuming Sheet2 is next to Sheet1). =Sheet2.\$A1 ... you get it =SUM(\$A\$1:\$A4) is the sum in A from top to this row (relative row 4) Syntax: Unquoted: Number or reference Double quoted: String Single quoted: 'Sheet name with Spaces' or row/column-label (see help) Practice: Copy/Drag arbitrary mixed/relative references across the document. Ctrl+F2 provides a pretty good helper for composing nested formulae with short descriptions of functions and arguments. Expert feature: Some functions can be used with array-formulas. Array-formulas are entered with Ctrl+Shift+Enter. They may return more than one value across many cells and the formula's functions may take arrays instead of values. For instance B2: =TRANSPOSE(A2:A10) That's almost all about the "spreadsheet language"._________________Rest in peace, oooforum.org Get help on http://forum.openoffice.org
carl
Super User

Joined: 21 Apr 2003
Posts: 920
Location: Germany

Posted: Sun Jan 28, 2007 1:12 am    Post subject:

 Quote: What I want to do is SUM(I4:I999) ONLY WHERE the H column="thistext" and put the necessary code to calculate this into a cell.

JohnV has answered your question but as you say you are new to calc perhaps you should have a look at the Datapilot. (in the DATA menu)
this enables you to create a sum of all the different types of spending you have in a neat table.
_________________
carl
Using OpenOffice.org 2 on XP sp2
Thunderace
General User

Joined: 27 Jan 2007
Posts: 5

 Posted: Mon Jan 29, 2007 12:32 am    Post subject: Thanks Villeroy that was very helpfull. Carl I try to use the code as opposed to "ready made" functions to enable me to get used to the application syntax. To everyone who answered, thanks, it was appreciated.
 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