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?