Excel 2007: =DSUM not working as expected

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

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 are:

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)
where:
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'
Click to expand...

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?
 
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
 
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.)
 
My formula does works on text value


Dan said:
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.)
Click to expand...
 
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
 
Back
Top