Excel 2007: =DSUM not working as expected

[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?

 

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

adjust your range to suit

copy down to the next 11 rows

 

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

 

[Teethless mama]

My formula does works on text value

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

 

[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