SUMIF for date

[Lamb Chop]

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

 

[Guest]

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

 

[Dave Peterson]

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.