Jeff Causey wrote:
Dennis,
You stated that you can't sort the data in the columns. I'd be
interested to know why that is, only because my first thought was that
would be the easy way.
One possible solution would be to create a third column containing an IF
function that matches the value from COL1 with another value you enter.
If they match, it fills in the value from COL2, otherwise it enters a 0.
I setup a test spreadsheet and I have a cell C2 where I enter my "test"
value (e.g. AAAAA). My formula in cell C5 is:
=IF(A5=$C$2;B5;0)
Depending on your data source, you may need to set this up in a separate
file and it could be cleaned up to make it easier to use (especially if
you'll be doing this a lot). But I think this should give you the basic
concept.
HTH,
Jeff Causey
Dennis Marks wrote:
Is this possible with a function or a feature of Calc. I have a
spreadsheet as follows:
COL1 COL2
AAAAA value
BBBBB value
CCCCC value
AAAAA value
BBBBB value
CCCCC value
etc
I would like a sum of value in col2 ONLY when item in column 1 is a
specific value. For example what is the sum of the values where col1
is equal to BBBBBB? I can't sort it and there can be a variable number
of records.
To be more specific I have payroll records for each employee. I would
like to sum records only for a specific date.
I've reverted to 2.0. Here's the relevant passage from the help files:
Conditions linked by AND can be used with the function SUM() in the
following manner:
Example assumption: You have entered invoices into a table. Column A
contains the date value of the invoice, column B the amounts. You want
to find a formula that you can use to return the total of all amounts
only for a specific month, e.g. only the amount for the period >=1.1.99
to <1.2.99. The range with the date values covers A1:A40, the range
containing the amounts to be totaled is B1:B40. C1 contains the start
date, 1.1.99, of the invoices to be included and C2 the date, 1.2.99,
that is no longer included.
Enter the following formula as an array formula:
=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)
In order to enter this as an array formula, you must press the Shift +
Ctrl + Enter key instead of simply pressing the Enter key to close the
formula. The formula will then be shown in the Formula bar enclosed in
braces.
{=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)}
The formula is based on the fact that the result of a comparison is 1,
if the criterion is met and 0 if it is not. The individual comparison
results will be treated as an array and used in matrix multiplication,
and at the end the individual values will be totaled to give the result
matrix. The SUM() function can also be used in this way, for example, as
COUNTIF() with several criteria.
There also appears to be a way to do this with the sumif() function, but
it's not totally clear to me what the syntax would be.
HTH
--
Rod
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
