DSUM gives a #VALUE! error and I can't work out why!

[Biff]

Hi!

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

=SUMPRODUCT(--(A2:A214>DATE(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:A214>C2),--(A2:A214<D2),B2:B214)

Biff

"Treasurer John" <Treasurer [email protected]> wrote in message
news:[email protected]...
> 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!

[Peo Sjoblom]

The most ideal solution would probably be a pivot table, select the table
(A2:B214?), do data>pivot table pivot chart report, select layout, drag the
date header to the row area and the money/amount header to the data area,
that should give you "Sum of Money" if your entries are numeric values,
click finish. Right click on any date in the pivot table that was created
and select group and show detail>group, select months and click OK. Will
group and sum monthly

Second choice if the table wasn't too big would be sumproduct

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

and one way to do DSUM would be

=DSUM(A2:B214,"Money",E1:F2)

where E1:F2 holds the criteria

=">"&DATE(2005,1,31)

="<"&DATE(2005,3,1)

in F1 and F2 and E1 and E2 hold the date header


--
Regards,

Peo Sjoblom

(No private emails please)


"Treasurer John" <Treasurer [email protected]> wrote in message
news:[email protected]...
> 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!

[Treasurer John]

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!

[Biff]

Hi!

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

=SUMPRODUCT(--(A2:A214>DATE(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:A214>C2),--(A2:A214<D2),B2:B214)

Biff

"Treasurer John" <Treasurer [email protected]> wrote in message
news:[email protected]...
> 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!

[Peo Sjoblom]

The most ideal solution would probably be a pivot table, select the table
(A2:B214?), do data>pivot table pivot chart report, select layout, drag the
date header to the row area and the money/amount header to the data area,
that should give you "Sum of Money" if your entries are numeric values,
click finish. Right click on any date in the pivot table that was created
and select group and show detail>group, select months and click OK. Will
group and sum monthly

Second choice if the table wasn't too big would be sumproduct

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

and one way to do DSUM would be

=DSUM(A2:B214,"Money",E1:F2)

where E1:F2 holds the criteria

=">"&DATE(2005,1,31)

="<"&DATE(2005,3,1)

in F1 and F2 and E1 and E2 hold the date header


--
Regards,

Peo Sjoblom

(No private emails please)


"Treasurer John" <Treasurer [email protected]> wrote in message
news:[email protected]...
> 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!