I need speed calculate when I filter rows.
 

hi
Iam
using:=SUMPRODUCT(SUBTOTAL(3,OFFSET(N11:N10000,ROW (N11:N10000)-ROW(N11),0,1)),--(N11:N10000="160m"))formula
in 50 cells and subtotal function in 10 cells and I am working in a bout
10000R*30C cells.When I use auto filter I must wait a few time to calculate
and ready.I need speed calculate.
sorry for language problems.
thanks

I need speed calculate when I filter rows.
 

If you click on Tools | Options | Calculation Tab and set calculation
to Manual rather than Automatic, this will greatly speed up Excel's
filtering. You can force a recalculation while in this mode by
pressing F9. Remember to set it back to Automatic when you are
finished.

Hope this helps.

Pete

On Jun 26, 1:05*pm, nader wrote:
hi
Iam
using:=SUMPRODUCT(SUBTOTAL(3,OFFSET(N11:N10000,ROW (N11:N10000)-ROW(N11),0,1*)),--(N11:N10000="160m"))formula
in 50 cells and subtotal function in 10 cells and I am working in a bout
10000R*30C cells.When I use auto filter I must wait a few time to calculate
and ready.I need speed calculate.
sorry for language problems.
thanks

I need speed calculate when I filter rows.
 

thanks ,but I want both of them.

"Pete_UK" wrote:

If you click on Tools | Options | Calculation Tab and set calculation
to Manual rather than Automatic, this will greatly speed up Excel's
filtering. You can force a recalculation while in this mode by
pressing F9. Remember to set it back to Automatic when you are
finished.

Hope this helps.

Pete

On Jun 26, 1:05 pm, nader wrote:
hi
Iam
using:=SUMPRODUCT(SUBTOTAL(3,OFFSET(N11:N10000,ROW (N11:N10000)-ROW(N11),0,1Â*)),--(N11:N10000="160m"))formula
in 50 cells and subtotal function in 10 cells and I am working in a bout
10000R*30C cells.When I use auto filter I must wait a few time to calculate
and ready.I need speed calculate.
sorry for language problems.
thanks

I need speed calculate when I filter rows.
 

Not sure what you mean. You can EITHER have calculation set to
Automatic OR to Manual - when in Manual you can force a recalculation
when you want to by pressing F9.

Pete

On Jun 26, 1:28*pm, nader wrote:
thanks ,but I want both of them.



"Pete_UK" wrote:
If you click on Tools | Options | Calculation Tab and set calculation
to Manual rather than Automatic, this will greatly speed up Excel's
filtering. You can force a recalculation while in this mode by
pressing F9. Remember to set it back to Automatic when you are
finished.

Hope this helps.

Pete

On Jun 26, 1:05 pm, nader wrote:
hi
Iam
using:=SUMPRODUCT(SUBTOTAL(3,OFFSET(N11:N10000,ROW (N11:N10000)-ROW(N11),0,1**)),--(N11:N10000="160m"))formula
in 50 cells and subtotal function in 10 cells and I am working in a bout
10000R*30C cells.When I use auto filter I must wait a few time to calculate
and ready.I need speed calculate.
sorry for language problems.
thanks- Hide quoted text -

- Show quoted text -

I need speed calculate when I filter rows.
 

sorry, I want filtering and calculating synchronous.is this possible fast.

"Pete_UK" wrote:

Not sure what you mean. You can EITHER have calculation set to
Automatic OR to Manual - when in Manual you can force a recalculation
when you want to by pressing F9.

Pete

On Jun 26, 1:28 pm, nader wrote:
thanks ,but I want both of them.



"Pete_UK" wrote:
If you click on Tools | Options | Calculation Tab and set calculation
to Manual rather than Automatic, this will greatly speed up Excel's
filtering. You can force a recalculation while in this mode by
pressing F9. Remember to set it back to Automatic when you are
finished.

Hope this helps.

Pete

On Jun 26, 1:05 pm, nader wrote:
hi
Iam
using:=SUMPRODUCT(SUBTOTAL(3,OFFSET(N11:N10000,ROW (N11:N10000)-ROW(N11),0,1Â*Â*)),--(N11:N10000="160m"))formula
in 50 cells and subtotal function in 10 cells and I am working in a bout
10000R*30C cells.When I use auto filter I must wait a few time to calculate
and ready.I need speed calculate.
sorry for language problems.
thanks- Hide quoted text -

- Show quoted text -

I need speed calculate when I filter rows.
 

OFFSET is a volatile function, so if there is a change in the
worksheet (like choosing a filter) then Excel will recalculate these
formulae (and dependents) and this is obviously slowing you down.
Perhaps you could look at other ways of achieving the results by
amending the formulae that you have.

Hope this helps.

Pete

On Jun 26, 2:19*pm, nader wrote:
sorry, I want filtering and calculating synchronous.is this possible fast..



"Pete_UK" wrote:
Not sure what you mean. You can EITHER have calculation set to
Automatic OR to Manual - when in Manual you can force a recalculation
when you want to by pressing F9.

Pete

On Jun 26, 1:28 pm, nader wrote:
thanks ,but I want both of them.

"Pete_UK" wrote:
If you click on Tools | Options | Calculation Tab and set calculation
to Manual rather than Automatic, this will greatly speed up Excel's
filtering. You can force a recalculation while in this mode by
pressing F9. Remember to set it back to Automatic when you are
finished.

Hope this helps.

Pete

On Jun 26, 1:05 pm, nader wrote:
hi
Iam
using:=SUMPRODUCT(SUBTOTAL(3,OFFSET(N11:N10000,ROW (N11:N10000)-ROW(N11),0,1***)),--(N11:N10000="160m"))formula
in 50 cells and subtotal function in 10 cells and I am working in a bout
10000R*30C cells.When I use auto filter I must wait a few time to calculate
and ready.I need speed calculate.
sorry for language problems.
thanks- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -