At 15:53 12/10/2007 +0100, Victor Domingos wrote:
I have a table where column B is storing dates and column C is
storing money values referring to those dates. In another table, I
would like to sum the money values for each week. I have been trying
to accomplish this by creating a table with column with the initial
day of each week (for the column named "Week") and using the
function COUNTIF. However, I am not able to specify two conditions
or criteria for that function. Is there any way to say Calc to sum
something in different cells if a DATE value is between X and Y?
I imagine that you mean the SUMIF() function rather than COUNTIF(),
but - as you have discovered - it is difficult to see how you can
combine two conditions with SUMIF().
There is a way of achieving what you need using SUM(), and it is
described in the help text for the SUM() function. It relies on the
fact that logical values, whilst being displayed as TRUE and FALSE,
are actually stored as 1 and 0. The trick, then, is to construct the
two conditions and then to misinterpret them as numbers. If you
multiply your money values by both these logical values, you will be
multiplying each by two values, each of which will either be 0 or
1. This will hide, as it were, all values except those for which
both multipliers are 1 - in other words, for which both conditions
are true. You can then sum these expressions to form the sum of just
the unhidden values: those that fall in your chosen week.
Let's imagine that your starting date for the week is in cell D1. In
the cell where you want the monetary sum, enter:
=SUM((B1:B100>=D1)*(B1:B100<D1+7)*C1:C100)
You are not quite there, as this is an array formula, even though it
returns a single result. So once you have constructed the formula,
you need to use Ctrl+Shift+Enter to confirm it, rather than simple
Enter. The formula will then appear in the Input line as:
{=SUM((B1:B100>=D1)*(B1:B100<D1+7)*C1:C100)}
complete with enclosing braces (but note that you cannot choose to
type those braces yourself).
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
