Hi! I've been trying the method you suggested, but there seems to be
something wrong. I am trying to send a .ods attached with this e-
mail, I don't know if the mailing list allows this...
I appreciate your help.
Thanks,
Victor Domingos
http://lojamac.com/blog
----
Em 2007/10/12, às 18:54, Brian Barker escreveu:
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
