Summing values to today - tidier version

[Joey]

I am attempting to create a spreadsheet in which I want to determine the sum
of a list of values corresponding to dates up to and including today

The spreadsheet below contains a sample list of data.

In the example the sum of values up to and including today, 19 Mar 04, is
19. I expected the formula in Cell D2 to return this sum but it doesn't. The
formula in D4 does. The formulas are:

D2: =SUMIF(A:A,"<=TODAY()",B:B)
D4: =SUMIF(C:C,"<=19",B:B)

Am I expecting the impossible?

If I am not, what should the formula in Cell D2 be?

(Another question: I attempted to send this spreadsheet as an attachment but
I just getting errors telling me I couldn't post to the newsserver: I use
Otlook Express as my newsreader. Any suggestions why I can't post with an
attachment?)

A B C D
1 Date Value
2 1 Mar 04 1 1 0
3 2 Mar 04 1 2
4 3 Mar 04 1 3 19
5 4 Mar 04 1 4
6 5 Mar 04 1 5
7 6 Mar 04 1 6
8 7 Mar 04 1 7
9 8 Mar 04 1 8
10 9 Mar 04 1 9
11 10 Mar 04 1 10
12 11 Mar 04 1 11
13 12 Mar 04 1 12
14 13 Mar 04 1 13
15 14 Mar 04 1 14
16 15 Mar 04 1 15
17 16 Mar 04 1 16
18 17 Mar 04 1 17
19 18 Mar 04 1 18
20 19 Mar 04 1 19
21 20 Mar 04 1 20
22 21 Mar 04 1 21
23 22 Mar 04 1 22
24 23 Mar 04 1 23
25 24 Mar 04 1 24
26 25 Mar 04 1 25
27 26 Mar 04 1 26
28 27 Mar 04 1 27
29 28 Mar 04 1 28
30 29 Mar 04 1 29
31 30 Mar 04 1 30
32 31 Mar 04 1 31

 

[Frank Kabel]

Hi
try
=SUMIF(A:A,"<=" & TODAY(),B:B)

 

[Andy B]

Hi

Try:
=SUMIF(A:A,"<="&TODAY(),B:B)

If you do attach a sheet, not many people will open it - due to possible
virus problems. Plain text, like you did, is fine.

Andy.

 

[Joey]

To Frank Kabel and Andy B,

Thanks to both of you for helpful and very speedy responses: your
suggestions work.

Thanks, too, Andy for your hint about attachments. I'm a slow learner but I
stay learnt'

 

[Dave Hawley]

Hi Joey

SUNIF will do the job as has been said. However, you may like to
consider the DSUM which is very flexible when working with dates. If you
go here: http://www.ozgrid.com/download/default.htm and download
"DFunctionsWithValidation.zip" it should give you some ideas.

BTW. you may also like to try our Free Excel forum when needing to post
formatted Worksheets.

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Joey]

Thanks to Dave and Arvi, as well: