Excel 2007: =DSUM not working as expected
 

I have a reporting spreadsheet with two workbooks.
Workbook 'Data' contains rows of data, lots of columns but two that are
relevant: 'JulianDay' and 'Transactions'. The First Few rows a

JulianDay Transactions
001 140
002 1298
003 166
....
031 197
032 151
....
etc.

In Workbook 'Report', I am attempting to sum the number of transactions for
January - i.e. JulianDay = 001 and JulianDay <=031.

I do this for each month - i.e. 12 DSUM formulas. In EVERY formula, DSUM
excludes the first day of the month. I am stumpted as to why.

The DSUM formula is: =DSUM(Data!$A$1:$Y$369,C$4,CriteriaJan)
whe
Data!$A$1:$Y$369 is the data range;
C$4 contains the text value "Transactions", and
CriteriaJan is a range that looks like this:
JulianDay JulianDay
='001' <='031'

I have built the criteria (e.g. ='001' or <='031') with a text formula that
works for every JulianDay except the first day of the month.

An example of the text formula that builds the criteria looks like this:
="='" &TEXT(K5,0)&"'", where
K5, in this case, contains the numeric value 244 (The Julian Day for Sept 1)

Can anyone offer any suggestions on what I am doing wrong or does anyone see
anything silly I have overlooked?

Excel 2007: =DSUM not working as expected
 

Assume the Julian days are numeric values not text values.

=SUMPRODUCT(--(MONTH(DATE(YEAR(TODAY()),1,$A$1:$A$40))=ROWS($1:1 )),$B$1:$B$40)

adjust your range to suit

copy down to the next 11 rows



"Dan" wrote:

I have a reporting spreadsheet with two workbooks.
Workbook 'Data' contains rows of data, lots of columns but two that are
relevant: 'JulianDay' and 'Transactions'. The First Few rows a

JulianDay Transactions
001 140
002 1298
003 166
...
031 197
032 151
...
etc.

In Workbook 'Report', I am attempting to sum the number of transactions for
January - i.e. JulianDay = 001 and JulianDay <=031.

I do this for each month - i.e. 12 DSUM formulas. In EVERY formula, DSUM
excludes the first day of the month. I am stumpted as to why.

The DSUM formula is: =DSUM(Data!$A$1:$Y$369,C$4,CriteriaJan)
whe
Data!$A$1:$Y$369 is the data range;
C$4 contains the text value "Transactions", and
CriteriaJan is a range that looks like this:
JulianDay JulianDay
='001' <='031'

I have built the criteria (e.g. ='001' or <='031') with a text formula that
works for every JulianDay except the first day of the month.

An example of the text formula that builds the criteria looks like this:
="='" &TEXT(K5,0)&"'", where
K5, in this case, contains the numeric value 244 (The Julian Day for Sept 1)

Can anyone offer any suggestions on what I am doing wrong or does anyone see
anything silly I have overlooked?

Excel 2007: =DSUM not working as expected
 

The JulianDays column is text, exactly three characters, left-zero filled
(i.e. '001' or '031') and comes from an external data source.

Does anyone know if the criteria "=" (Greater than or equal to) or "<="
are valid? All of the Microsoft examples I have found only use "<" or "".
I have yet to see an example that adds "or equal to" to the criteria. (It
looks like both DSUM and SUMIF use the same syntax for criteria.)

"Teethless mama" wrote:

Assume the Julian days are numeric values not text values.

=SUMPRODUCT(--(MONTH(DATE(YEAR(TODAY()),1,$A$1:$A$40))=ROWS($1:1 )),$B$1:$B$40)

adjust your range to suit

copy down to the next 11 rows



"Dan" wrote:

I have a reporting spreadsheet with two workbooks.
Workbook 'Data' contains rows of data, lots of columns but two that are
relevant: 'JulianDay' and 'Transactions'. The First Few rows a

JulianDay Transactions
001 140
002 1298
003 166
...
031 197
032 151
...
etc.

In Workbook 'Report', I am attempting to sum the number of transactions for
January - i.e. JulianDay = 001 and JulianDay <=031.

I do this for each month - i.e. 12 DSUM formulas. In EVERY formula, DSUM
excludes the first day of the month. I am stumpted as to why.

The DSUM formula is: =DSUM(Data!$A$1:$Y$369,C$4,CriteriaJan)
whe
Data!$A$1:$Y$369 is the data range;
C$4 contains the text value "Transactions", and
CriteriaJan is a range that looks like this:
JulianDay JulianDay
='001' <='031'

I have built the criteria (e.g. ='001' or <='031') with a text formula that
works for every JulianDay except the first day of the month.

An example of the text formula that builds the criteria looks like this:
="='" &TEXT(K5,0)&"'", where
K5, in this case, contains the numeric value 244 (The Julian Day for Sept 1)

Can anyone offer any suggestions on what I am doing wrong or does anyone see
anything silly I have overlooked?

Excel 2007: =DSUM not working as expected
 

My formula does works on text value


"Dan" wrote:

The JulianDays column is text, exactly three characters, left-zero filled
(i.e. '001' or '031') and comes from an external data source.

Does anyone know if the criteria "=" (Greater than or equal to) or "<="
are valid? All of the Microsoft examples I have found only use "<" or "".
I have yet to see an example that adds "or equal to" to the criteria. (It
looks like both DSUM and SUMIF use the same syntax for criteria.)

"Teethless mama" wrote:

Assume the Julian days are numeric values not text values.

=SUMPRODUCT(--(MONTH(DATE(YEAR(TODAY()),1,$A$1:$A$40))=ROWS($1:1 )),$B$1:$B$40)

adjust your range to suit

copy down to the next 11 rows



"Dan" wrote:

I have a reporting spreadsheet with two workbooks.
Workbook 'Data' contains rows of data, lots of columns but two that are
relevant: 'JulianDay' and 'Transactions'. The First Few rows a

JulianDay Transactions
001 140
002 1298
003 166
...
031 197
032 151
...
etc.

In Workbook 'Report', I am attempting to sum the number of transactions for
January - i.e. JulianDay = 001 and JulianDay <=031.

I do this for each month - i.e. 12 DSUM formulas. In EVERY formula, DSUM
excludes the first day of the month. I am stumpted as to why.

The DSUM formula is: =DSUM(Data!$A$1:$Y$369,C$4,CriteriaJan)
whe
Data!$A$1:$Y$369 is the data range;
C$4 contains the text value "Transactions", and
CriteriaJan is a range that looks like this:
JulianDay JulianDay
='001' <='031'

I have built the criteria (e.g. ='001' or <='031') with a text formula that
works for every JulianDay except the first day of the month.

An example of the text formula that builds the criteria looks like this:
="='" &TEXT(K5,0)&"'", where
K5, in this case, contains the numeric value 244 (The Julian Day for Sept 1)

Can anyone offer any suggestions on what I am doing wrong or does anyone see
anything silly I have overlooked?

Excel 2007: =DSUM not working as expected
 

Hi,

Your best option would be to convert the Julian dates to numeric values and
then use the criteria as =1 and <=9

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Dan" wrote in message
...
I have a reporting spreadsheet with two workbooks.
Workbook 'Data' contains rows of data, lots of columns but two that are
relevant: 'JulianDay' and 'Transactions'. The First Few rows a

JulianDay Transactions
001 140
002 1298
003 166
...
031 197
032 151
...
etc.

In Workbook 'Report', I am attempting to sum the number of transactions
for
January - i.e. JulianDay = 001 and JulianDay <=031.

I do this for each month - i.e. 12 DSUM formulas. In EVERY formula, DSUM
excludes the first day of the month. I am stumpted as to why.

The DSUM formula is: =DSUM(Data!$A$1:$Y$369,C$4,CriteriaJan)
whe
Data!$A$1:$Y$369 is the data range;
C$4 contains the text value "Transactions", and
CriteriaJan is a range that looks like this:
JulianDay JulianDay
='001' <='031'

I have built the criteria (e.g. ='001' or <='031') with a text formula
that
works for every JulianDay except the first day of the month.

An example of the text formula that builds the criteria looks like this:
="='" &TEXT(K5,0)&"'", where
K5, in this case, contains the numeric value 244 (The Julian Day for Sept
1)

Can anyone offer any suggestions on what I am doing wrong or does anyone
see
anything silly I have overlooked?