SUMIF for date
 

I would like to add the number from a range of date e.g. may 15, 2006 to
June 17, 2006

The data arrange in this way.

...
...
....
......
14-5-2006 19
15-5-2006 12
16-5-2006 18
... ...

... ..

... ..

... ..
17-6-2006 7
18-6-2006 9
..... ..
.... ..
... ..
.... ..


I want to get a number adding all B cells if the corresponding A cell is
within the range.

I used this formula.


=SUMIF(A1:A10, "=DATEVALUE("15/05/06")", B1:B10) - SUMIF(A1:A10,
"<DATEVALUE("18/06/06")", B1:B10)

but it does not work

Thanks

SUMIF for date
 

Try removing the quotation marks around the DATEVALUE function:

=SUMIF(A1:A10,=DATEVALUE(15/05/06),B1:B10) -
SUMIF(A1:A10,DATEVALUE(18/06/06),B1:B10)

You may also want to experiment with converting the dates to serial numbers
by formatting them as numbers, not dates. If you were to do that then you
could get rid of the DATEVALUE function entirely and just SUMIF based on
numerical values.

Dave
--
Brevity is the soul of wit.


"Lamb Chop" wrote:

I would like to add the number from a range of date e.g. may 15, 2006 to
June 17, 2006

The data arrange in this way.

...
...
....
......
14-5-2006 19
15-5-2006 12
16-5-2006 18
... ...

... ..

... ..

... ..
17-6-2006 7
18-6-2006 9
..... ..
.... ..
... ..
.... ..


I want to get a number adding all B cells if the corresponding A cell is
within the range.

I used this formula.


=SUMIF(A1:A10, "=DATEVALUE("15/05/06")", B1:B10) - SUMIF(A1:A10,
"<DATEVALUE("18/06/06")", B1:B10)

but it does not work

Thanks

SUMIF for date
 

I think I'd use:

=SUMIF(A1:A10,"="&date(2006,5,15), B1:B10)
- SUMIF(A1:A10,"<"&DATE(2006,6,18), B1:B10)

But you could try:

=SUMIF(A1:A10, "="&DATEVALUE("15/05/06"), B1:B10)
- SUMIF(A1:A10,"<"&DATEVALUE("18/06/06"), B1:B10)

But using =datevalue() like this scares me.

For me with my USA (mdy) settings, =datevalue("15/05/06") won't work.

And rather than rely on an ambiguous date (=datevalue("03/04/05")), why not just
remove all doubt.




Lamb Chop wrote:

I would like to add the number from a range of date e.g. may 15, 2006 to
June 17, 2006

The data arrange in this way.

..
..
...
.....
14-5-2006 19
15-5-2006 12
16-5-2006 18
.. ...

.. ..

.. ..

.. ..
17-6-2006 7
18-6-2006 9
.... ..
... ..
.. ..
... ..

I want to get a number adding all B cells if the corresponding A cell is
within the range.

I used this formula.

=SUMIF(A1:A10, "=DATEVALUE("15/05/06")", B1:B10) - SUMIF(A1:A10,
"<DATEVALUE("18/06/06")", B1:B10)

but it does not work

Thanks

--

Dave Peterson