Summing values to today - tidier version

  • Thread starter Thread starter Joey
  • Start date Start date
J

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
 
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.
 
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'
 
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 *****
 
Back
Top