I have one small section on a larger Totals spreadsheet which happens to be
picking up names and hours from external workbooks. This year's Totals
spreadsheet is identical to last year's, but when I put in test data in the
first external workbook for this year, which appears on the first row of this
small section of the Totals spreadsheet, I noticed there were no totals on the
bottom. The formula is a simple SUM(D173:D185). After making sure that the
first row was included in the formula, I highlighted the entire column and
hit AutoSum, which should have created the same formula, but it didn't.
Instead I noticed that when I clicked on Autosum the first row, the only one
with data, un-highlighted, and the SUM expression at the bottom of the column
started at the second row, In other words, AutSum would not include the one
row
with data.
I tested this again by adding similar data to a second workbook, which
appears on the second row of this section, and found the same result. No
totals
at the bottom, and when I used AutoSum to re-create the SUM expression,
highlighted the column again and hit AutoSum, the first and second row
unhighlighted, and the AutoSum created SUM expression started at the third
row, thus skipping the two rows with data.
I checked the type, which was General for the data portion of the section
and Number for the totals line (so I could control the number of decimals in
the total). This is how last year's spreadsheet is set up, and it works fine.
Changing the type for the whole grid to General makes no difference. Using
only whole numbers makes no difference.
I also cleared the contents of this section and re-entered the INDIRECT
formula in all the cells to make sure there was no contamination somewhere.
That had no effect on the problem.
I re-booted, and opened the spreadsheet again, that had no effect.
This whole spreadsheet consists of columns of numbers derived from INDIRECT
formulas which get data from other workbooks. I have never had a problem
totaling those columns, and even on the section in question, while there are
three Hours columns, all of which have the same problem, the Total column on
the right, which Totals the totals using the same SUM expression at the
bottom does work.
Finally, I changed the SUM expression so that instead of using the ":" I
listed each cell in the column individually separated by plus signs. That
worked.
So what would cause Sum (:) to produce no result, or the AutoSum to reject
rows from the column highlighted?
Thanks,
Eric

Your data aren't really numbers--they're text masquerading as numbers.

Debra Dalgleish explains how to fix these for a different problem:
http://contextures.com/xlFunctions02.html#Trouble

Eric wrote:

I have one small section on a larger Totals spreadsheet which happens to be
picking up names and hours from external workbooks. This year's Totals
spreadsheet is identical to last year's, but when I put in test data in the
first external workbook for this year, which appears on the first row of this
small section of the Totals spreadsheet, I noticed there were no totals on the
bottom. The formula is a simple SUM(D173:D185). After making sure that the
first row was included in the formula, I highlighted the entire column and
hit AutoSum, which should have created the same formula, but it didn't.
Instead I noticed that when I clicked on Autosum the first row, the only one
with data, un-highlighted, and the SUM expression at the bottom of the column
started at the second row, In other words, AutSum would not include the one
row
with data.
I tested this again by adding similar data to a second workbook, which
appears on the second row of this section, and found the same result. No
totals
at the bottom, and when I used AutoSum to re-create the SUM expression,
highlighted the column again and hit AutoSum, the first and second row
unhighlighted, and the AutoSum created SUM expression started at the third
row, thus skipping the two rows with data.
I checked the type, which was General for the data portion of the section
and Number for the totals line (so I could control the number of decimals in
the total). This is how last year's spreadsheet is set up, and it works fine.
Changing the type for the whole grid to General makes no difference. Using
only whole numbers makes no difference.
I also cleared the contents of this section and re-entered the INDIRECT
formula in all the cells to make sure there was no contamination somewhere.
That had no effect on the problem.
I re-booted, and opened the spreadsheet again, that had no effect.
This whole spreadsheet consists of columns of numbers derived from INDIRECT
formulas which get data from other workbooks. I have never had a problem
totaling those columns, and even on the section in question, while there are
three Hours columns, all of which have the same problem, the Total column on
the right, which Totals the totals using the same SUM expression at the
bottom does work.
Finally, I changed the SUM expression so that instead of using the ":" I
listed each cell in the column individually separated by plus signs. That
worked.
So what would cause Sum (:) to produce no result, or the AutoSum to reject
rows from the column highlighted?
Thanks,
Eric

--

Dave Peterson

Eric

Have you tried using the formula auditing toolbar Trace Precedents command
to see where the non-working formula is deriving it's info from?
--
OneFineDay


"Eric" wrote:

I have one small section on a larger Totals spreadsheet which happens to be
picking up names and hours from external workbooks. This year's Totals
spreadsheet is identical to last year's, but when I put in test data in the
first external workbook for this year, which appears on the first row of this
small section of the Totals spreadsheet, I noticed there were no totals on the
bottom. The formula is a simple SUM(D173:D185). After making sure that the
first row was included in the formula, I highlighted the entire column and
hit AutoSum, which should have created the same formula, but it didn't.
Instead I noticed that when I clicked on Autosum the first row, the only one
with data, un-highlighted, and the SUM expression at the bottom of the column
started at the second row, In other words, AutSum would not include the one
row
with data.
I tested this again by adding similar data to a second workbook, which
appears on the second row of this section, and found the same result. No
totals
at the bottom, and when I used AutoSum to re-create the SUM expression,
highlighted the column again and hit AutoSum, the first and second row
unhighlighted, and the AutoSum created SUM expression started at the third
row, thus skipping the two rows with data.
I checked the type, which was General for the data portion of the section
and Number for the totals line (so I could control the number of decimals in
the total). This is how last year's spreadsheet is set up, and it works fine.
Changing the type for the whole grid to General makes no difference. Using
only whole numbers makes no difference.
I also cleared the contents of this section and re-entered the INDIRECT
formula in all the cells to make sure there was no contamination somewhere.
That had no effect on the problem.
I re-booted, and opened the spreadsheet again, that had no effect.
This whole spreadsheet consists of columns of numbers derived from INDIRECT
formulas which get data from other workbooks. I have never had a problem
totaling those columns, and even on the section in question, while there are
three Hours columns, all of which have the same problem, the Total column on
the right, which Totals the totals using the same SUM expression at the
bottom does work.
Finally, I changed the SUM expression so that instead of using the ":" I
listed each cell in the column individually separated by plus signs. That
worked.
So what would cause Sum (:) to produce no result, or the AutoSum to reject
rows from the column highlighted?
Thanks,
Eric