Troubleshooting the SUBTOTAL Command

  • Thread starter Thread starter James
  • Start date Start date
J

James

I'm trying to use the SUBTOTAL command to sum up columns
of filtered data.
I'm using "Advanced Filter".
I have 56 columns of simple numerical data. I have 2
other columns with data retrieved with VLOOKUP formulas.
When I filter my list using 3 rows of criteria for the
advanced filter, the subtotals will recalculate properly
for the 2 columns with formulas, but do not recalculate
for the other 56 simple columns.
If I go into the formula bar and hit "Enter" in each of
the 56 SUBTOTAL formulas, they do recalculate properly.
Why don't they automatically recalulate though? Are there
any alternatives that will work better?
Thanks for any help!
 
-----Original Message-----
I'm trying to use the SUBTOTAL command to sum up columns
of filtered data.
I'm using "Advanced Filter".
I have 56 columns of simple numerical data. I have 2
other columns with data retrieved with VLOOKUP formulas.
When I filter my list using 3 rows of criteria for the
advanced filter, the subtotals will recalculate properly
for the 2 columns with formulas, but do not recalculate
for the other 56 simple columns.
If I go into the formula bar and hit "Enter" in each of
the 56 SUBTOTAL formulas, they do recalculate properly.
Why don't they automatically recalulate though? Are there
any alternatives that will work better?
Thanks for any help!
.
Click to expand...

I also have SUM commands in merged cells under each group
of 4 SUBTOTALs (example: H275 =SUM(H274:K274), where H275
is actually a merged cell containing H275:K275). I now
realize that even when I manually force the SUBTOTALs to
recalculate, the SUMs underneath do not automatically
update either. What would cause this group of cells not
to recalculate, when the rest of the workbook seems to
work fine. Could I have mistakenly put some kind of
strange formatting or protection on these cells without
knowing about it? Please help...

Thanks.
 
Did you check the Automatic Calculation?
Go to Tools...Options...Calculation tab...then make sure the Automatic button is checked. After that click on Calc. sheet button and see if that helps. I am having the same type of problem and this seemed to work for me for now.
 
Yes, automatic calculation is on. 2 of the subtotals
work properly, but the others do not recalculate. I
still haven't figured this out yet.

-----Original Message-----
Did you check the Automatic Calculation?
Go to Tools...Options...Calculation tab...then make sure
Click to expand...
the Automatic button is checked. After that click on
Calc. sheet button and see if that helps. I am having
the same type of problem and this seemed to work for me
for now.
 
Back
Top