One way would be to enter your date criteria in cells, that you can then
reference in the formula, so that you could change the dates without having
to change the actual formula itself.

Say C1 = start date
And C2 = end date, which in your case would be 31.1.2000:

=SUMPRODUCT((B1:B10=C1)*(B1:B10<=C2)*A1:A10)/SUMPRODUCT((B1:B10=C1)*(B1:B10<C2))

Another way could be this *array* formula:

=AVERAGE(IF((B1:B10=C1)*(B1:B10<=C2),A1:A10))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Jan Kucera" wrote in message
...
Okay, sorry guys, did not realized that I use another locale, that was
just for example to simplify the thing I'm trying to do.
They are 1/1/2000 and 2/1/2000.

Jan

"Ragdyer" wrote in message
...
I might have guessed that if he had at least used 2007 somewhere in
there,
instead of making myself appear dense.<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
They are dates from another regional setting so one should be able to
use
the DATE function as the criteria


--

Regards,

Peo Sjoblom






"Ragdyer" wrote in message
...
Can you elaborate on the figures that you're using as your criteria?

If not typos, 1.1.2000 and 1.2.200 are what ... exactly?

--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!

--------------------------------------------------------------------------
-
"Jan Kucera" wrote in message
...
Okay,
is there a similar math trick for average of values in range?
Like AVERAGEIF(B1:B10, =1.1.2000 AND < 1.2.2000, A1:A10)

Thanks, Jan



"T. Valko" wrote in message
...
For the sum if, try this:

=SUMPRODUCT(--(A1:A10=10),--(A1:A10<=20),A1:A10)

For the average if is odd, try this array formula** :

=AVERAGE(IF(MOD(A1:A10,2),A1:A10))

The ISODD/ISEVEN functions won't work on arrays. Also, I'm assuming
the
values in question are integers and there are no text entries in
the
range.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If you're using Excel 2007 there is an AVERAGEIF function but I
don't
use
Excel 2007 so I don't know the correct syntax. The above array
formula
will work in any version.

--
Biff
Microsoft Excel MVP


"Jan Kucera" wrote in message
...
Hi,
I want to use formula in condition syntax, but I don't know how
to
reference the value being tested.

Like:
SUMIF(A1:A10;"10 AND <20")

Or:
AVERAGEIF(A1:A10;"ISODD(...)")

Thanks for any ideas.

Jan