View Single Post
  #2   Report Post  
Biff
 
Posts: n/a
Default

Hi!

If you can, use SUMPRODUCT. It's MUCH easier:

=SUMPRODUCT(--(A2:A214DATE(2005,1,31)),--(A2:A214<DATE(2005,3,1)),B2:B214)

Or, use 2 cells to hold the criteria:

C2 = 1/31/2005
D2 = 3/1/2005

=SUMPRODUCT(--(A2:A214C2),--(A2:A214<D2),B2:B214)

Biff

"Treasurer John" <Treasurer wrote in message
...
Having upgraded from Excel 97 to Excel 2003 I have a problem with some of
my
calculations in existing spreadsheets. Using DSUM the database is the
results
of an Access query, (written in the formula as
Query_from_MS_Access_Database), the field is the range of column B from
the
query listing amounts of money (written in the formula as B2:B214) and the
criteria is the field range encompasing
31/01/2005 <1/03/2005 in fields below ones saying Date, i.e. the effect
should be to sum all the amounts in the database query (column B) which
have the date (column A headed Date) in February 05.


I can't find anything wrong in the formula and the 'trace precedents'
lines
all point to the right places. The 'circle invalid data' option doesn't
circle anything. The dates are in the same format in column A as in the
criteria fields.

Using the 'Trace error' function it points to the header of column A which
says Date and the field in the criteria column which also says Date. The
error message expands to 'a value in the formula is of the wrong data
type'.
I can't work out, if this is referring to the fields saying Date, what
could
possibly be wrong with the data type.

Thanks for your help!