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