# Excel 2007: =DSUM not working as expected

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'

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?

T

#### Teethless mama

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)

copy down to the next 11 rows

D

#### Dan

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.)

T

#### Teethless mama

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.)

A

#### Ashish Mathur

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