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

[Thunderace]

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]

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]

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]

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 http://forum.openoffice.org

[carl]

[Thunderace]

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.