Since the upgrade to Office07 (yuck), pushing the AutoSum button, or Alt+=
gives me a SUBTOTAL() function instead of SUM. Of course I can manually type
the SUM formula, but that isn't the point. I'm trying to sum a few general
ledger journal entries to validate that they equal zero, and am now getting
totals because it isn't summing the whole, but the SUBTOTAL pieces only - and
who needs normally? Anyway, other than re-mapping my keyboard shortcut, or
manually typing SUM(), is there any way to direct AutoSum to really do a
AutoSum, not a SUBTOTAL?
Thanks!

I understand your frustration with the AutoSum function in Excel. The reason why you are getting a SUBTOTAL function instead of a SUM function is because Excel is trying to be smart and assume that you want to perform a subtotal calculation on a range of data. However, you can easily change this behavior by following these steps:

1. Click on the cell where you want to insert the SUM function.
2. Click on the AutoSum button or press Alt+= on your keyboard.
3. Excel will automatically select the range of cells that it thinks you want to sum. If this range is incorrect, simply click and drag to select the correct range of cells.
4. Instead of pressing Enter to insert the SUBTOTAL function, press Shift+Enter. This will force Excel to insert a SUM function instead.

Alternatively, you can also use the

Code:

=SUM(

function directly by typing "

Code:

=SUM(

" and then selecting the range of cells that you want to sum. This will give you the same result as using the AutoSum button, but without the risk of Excel trying to be too smart.

I hope this helps you get the results you need without having to manually type the SUM function every time. Let me know if you have any other questions!

I could not replicate what you mentioned.

I entered some numbers in A1:A10, selected the range and clicked on Auto Sum
button and got =SUM(A1:A10). Same result with Alt-=.

I also inserted Subtotals and tried the above. I again got Sum and not
Subtotal.

Perhaps if you can describe the steps you are taking...
"Pearl" wrote:

Since the upgrade to Office07 (yuck), pushing the AutoSum button, or Alt+=
gives me a SUBTOTAL() function instead of SUM. Of course I can manually type
the SUM formula, but that isn't the point. I'm trying to sum a few general
ledger journal entries to validate that they equal zero, and am now getting
totals because it isn't summing the whole, but the SUBTOTAL pieces only - and
who needs normally? Anyway, other than re-mapping my keyboard shortcut, or
manually typing SUM(), is there any way to direct AutoSum to really do a
AutoSum, not a SUBTOTAL?
Thanks!

If you have a filter applied then it will automatically select subtotal
instead of sum since subtotal ignores filtered rows
whereas sum will sum all rows

--


Regards,


Peo Sjoblom

"Pearl" wrote in message
...
Since the upgrade to Office07 (yuck), pushing the AutoSum button, or Alt+=
gives me a SUBTOTAL() function instead of SUM. Of course I can manually
type
the SUM formula, but that isn't the point. I'm trying to sum a few
general
ledger journal entries to validate that they equal zero, and am now
getting
totals because it isn't summing the whole, but the SUBTOTAL pieces only -
and
who needs normally? Anyway, other than re-mapping my keyboard shortcut,
or
manually typing SUM(), is there any way to direct AutoSum to really do a
AutoSum, not a SUBTOTAL?
Thanks!

Hello!
No filters. I have two journals that have negative sum of the numbers above
each one respectively as the opposing entry, and all nets to zero in total.
However, if I want to show a total row beneath all Excel is subtotaling the 2
individual summed totals above rather than summing the total of all the
lines, which you need to do to proof out journal entries on the ledger.

When I tried AutoSum, or even Alt+=, both insert SUBTOTAL beneath each
individual JE, rather than SUM(). When I hit AutoSum, I expect a SUM()
formula, not SUBTOTAL(), which is what I'm getting, which is what it used to
do. If I manually type SUM(), then everything works fine, but I just want to
change AutoSum to be the SUM() formula because in financial applications you
use SUM() way more than you do SUBTOTAL(). Any ideas?



"Peo Sjoblom" wrote:

If you have a filter applied then it will automatically select subtotal
instead of sum since subtotal ignores filtered rows
whereas sum will sum all rows

--


Regards,


Peo Sjoblom

"Pearl" wrote in message
...
Since the upgrade to Office07 (yuck), pushing the AutoSum button, or Alt+=
gives me a SUBTOTAL() function instead of SUM. Of course I can manually
type
the SUM formula, but that isn't the point. I'm trying to sum a few
general
ledger journal entries to validate that they equal zero, and am now
getting
totals because it isn't summing the whole, but the SUBTOTAL pieces only -
and
who needs normally? Anyway, other than re-mapping my keyboard shortcut,
or
manually typing SUM(), is there any way to direct AutoSum to really do a
AutoSum, not a SUBTOTAL?
Thanks!

I can't reproduce this on my 2007 unless I apply a filter.
When I do alt + = it sums and when I click the autosum icon it sums.

Btw, how does your subtotal formula look. If it is SUBTOTAL(9,Range)
then it is the equivalent of SUM(Range).

--


Regards,


Peo Sjoblom

"Pearl" wrote in message
...
Hello!
No filters. I have two journals that have negative sum of the numbers
above
each one respectively as the opposing entry, and all nets to zero in
total.
However, if I want to show a total row beneath all Excel is subtotaling
the 2
individual summed totals above rather than summing the total of all the
lines, which you need to do to proof out journal entries on the ledger.

When I tried AutoSum, or even Alt+=, both insert SUBTOTAL beneath each
individual JE, rather than SUM(). When I hit AutoSum, I expect a SUM()
formula, not SUBTOTAL(), which is what I'm getting, which is what it used
to
do. If I manually type SUM(), then everything works fine, but I just want
to
change AutoSum to be the SUM() formula because in financial applications
you
use SUM() way more than you do SUBTOTAL(). Any ideas?



"Peo Sjoblom" wrote:

If you have a filter applied then it will automatically select subtotal
instead of sum since subtotal ignores filtered rows
whereas sum will sum all rows

--


Regards,


Peo Sjoblom

"Pearl" wrote in message
...
Since the upgrade to Office07 (yuck), pushing the AutoSum button, or
Alt+=
gives me a SUBTOTAL() function instead of SUM. Of course I can
manually
type
the SUM formula, but that isn't the point. I'm trying to sum a few
general
ledger journal entries to validate that they equal zero, and am now
getting
totals because it isn't summing the whole, but the SUBTOTAL pieces
only -
and
who needs normally? Anyway, other than re-mapping my keyboard
shortcut,
or
manually typing SUM(), is there any way to direct AutoSum to really do
a
AutoSum, not a SUBTOTAL?
Thanks!

Yep, that is exactly what it is doing. When I select either AutoSum method
instead of Excel inserting a Sum() formula it is inserting a Subtotal()
formula, which I don't want. I've not typically had it do this. Now, the
original file has been saved as an Office07 file, but was downloaded from
BusinessObjects as a .xls file, though I'd not think that should have
anything to do with this. When I go to other files I'm getting the correct
Sum() formula, so perhaps it does?

"Peo Sjoblom" wrote:

I can't reproduce this on my 2007 unless I apply a filter.
When I do alt + = it sums and when I click the autosum icon it sums.

Btw, how does your subtotal formula look. If it is SUBTOTAL(9,Range)
then it is the equivalent of SUM(Range).

--


Regards,


Peo Sjoblom

"Pearl" wrote in message
...
Hello!
No filters. I have two journals that have negative sum of the numbers
above
each one respectively as the opposing entry, and all nets to zero in
total.
However, if I want to show a total row beneath all Excel is subtotaling
the 2
individual summed totals above rather than summing the total of all the
lines, which you need to do to proof out journal entries on the ledger.

When I tried AutoSum, or even Alt+=, both insert SUBTOTAL beneath each
individual JE, rather than SUM(). When I hit AutoSum, I expect a SUM()
formula, not SUBTOTAL(), which is what I'm getting, which is what it used
to
do. If I manually type SUM(), then everything works fine, but I just want
to
change AutoSum to be the SUM() formula because in financial applications
you
use SUM() way more than you do SUBTOTAL(). Any ideas?



"Peo Sjoblom" wrote:

If you have a filter applied then it will automatically select subtotal
instead of sum since subtotal ignores filtered rows
whereas sum will sum all rows

--


Regards,


Peo Sjoblom

"Pearl" wrote in message
...
Since the upgrade to Office07 (yuck), pushing the AutoSum button, or
Alt+=
gives me a SUBTOTAL() function instead of SUM. Of course I can
manually
type
the SUM formula, but that isn't the point. I'm trying to sum a few
general
ledger journal entries to validate that they equal zero, and am now
getting
totals because it isn't summing the whole, but the SUBTOTAL pieces
only -
and
who needs normally? Anyway, other than re-mapping my keyboard
shortcut,
or
manually typing SUM(), is there any way to direct AutoSum to really do
a
AutoSum, not a SUBTOTAL?
Thanks!

I notice that this is an old post but I am having the same issue. I recently changed from Excel 2003 to Excel 2010 and sometimes save workbooks backwardly compatible to the older version because my church uses it. I have noticed that my numbers do not calculate properly when using the "SUM" function but I'm not sure where the problem arose. What I see is "=SUBTOTAL(9,D74:D82)" but while working Excel 2010 if I click "SUM" and redo the function I receive "=SUM(D74:D82)". I dont' know what to do because there are too many of these occurrences for me to know where they are and change each and every one. Can anyone tell me how this happens so that I can avoid it in the future?