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

SUM a column of figures IF the column next to it has X text

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


Joined: 27 Jan 2007
Posts: 5

PostPosted: Sat Jan 27, 2007 6:52 am    Post subject: SUM a column of figures IF the column next to it has X text Reply with quote

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


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

PostPosted: Sat Jan 27, 2007 7:08 am    Post subject: Reply with quote

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


Joined: 27 Jan 2007
Posts: 5

PostPosted: Sat Jan 27, 2007 7:15 am    Post subject: Reply with quote

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 Very Happy So i don't have to learn a whole new language Rolling Eyes
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Jan 27, 2007 1:24 pm    Post subject: Reply with quote

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)<Ctrl+Shift+Enter>

That's almost all about the "spreadsheet language".
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
carl
Super User
Super User


Joined: 21 Apr 2003
Posts: 920
Location: Germany

PostPosted: Sun Jan 28, 2007 1:12 am    Post subject: Reply with quote

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


Joined: 27 Jan 2007
Posts: 5

PostPosted: Mon Jan 29, 2007 12:32 am    Post subject: Reply with quote

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.
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