Em 2007/10/12, às 18:54, Brian Barker escreveu:
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
Thanks! I will try this method.
Oh... and yes, I meant the SUMIF() funtion ;)
Victor Domingos
http://lojamac.com/blog
