Dear friends, I`m currently using CSE formulas to extract and analyse data
with the multiple criterias. e.g:
=SUM(('12SP'!$A$2:$A$65536=$A$1)*('12SP'!$G$2:$G$65536=$C$6)*('12SP'!$E$2:$E$65536<=$D$2)*('12SP'!$D$2:$D$65536))..But
unfortunately I have a problem with sheets so that everytime I face the
calculating cells %0..%100 phrase when I make any change. Perhaps it`s
because of heavy formulas..Anybody can help me in this issue?Thanks
beforehand.

One way .. I'd set the book's calc mode to Manual* (Click Tools > Options >
Calculation tab, options are there). Then we could press F9 to recalc, but
only as and when required, eg after all updates / changes are done.
*I'd usually leave "Recalc before save" unchecked (as a personal preference)

Another thing I might do is to use the smallest range sizes possible ..
> =SUM(('12SP'!$A$2:$A$65536=$A$1)*...
Do you really have/expect data all the way to 65K? Perhaps 1K suffices <g>?
The smaller the range sizes, the faster it'll compute.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abas Ibrahimov" wrote:
> Dear friends, I`m currently using CSE formulas to extract and analyse data
> with the multiple criterias. e.g:
> =SUM(('12SP'!$A$2:$A$65536=$A$1)*('12SP'!$G$2:$G$65536=$C$6)*('12SP'!$E$2:$E$65536<=$D$2)*('12SP'!$D$2:$D$65536))..But
> unfortunately I have a problem with sheets so that everytime I face the
> calculating cells %0..%100 phrase when I make any change. Perhaps it`s
> because of heavy formulas..Anybody can help me in this issue?Thanks
> beforehand.

Wow..thanks for help Max, I will try to calculate them manually..and reduce
the range size as much as possible. Thanks again, very helpful

"Max" wrote:

> One way .. I'd set the book's calc mode to Manual* (Click Tools > Options >
> Calculation tab, options are there). Then we could press F9 to recalc, but
> only as and when required, eg after all updates / changes are done.
> *I'd usually leave "Recalc before save" unchecked (as a personal preference)
>
> Another thing I might do is to use the smallest range sizes possible ..
> > =SUM(('12SP'!$A$2:$A$65536=$A$1)*...
> Do you really have/expect data all the way to 65K? Perhaps 1K suffices <g>?
> The smaller the range sizes, the faster it'll compute.
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Abas Ibrahimov" wrote:
> > Dear friends, I`m currently using CSE formulas to extract and analyse data
> > with the multiple criterias. e.g:
> > =SUM(('12SP'!$A$2:$A$65536=$A$1)*('12SP'!$G$2:$G$65536=$C$6)*('12SP'!$E$2:$E$65536<=$D$2)*('12SP'!$D$2:$D$65536))..But
> > unfortunately I have a problem with sheets so that everytime I face the
> > calculating cells %0..%100 phrase when I make any change. Perhaps it`s
> > because of heavy formulas..Anybody can help me in this issue?Thanks
> > beforehand.

You're welcome, Abas !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abas Ibrahimov" wrote:
> Wow..thanks for help Max, I will try to calculate them manually..and reduce
> the range size as much as possible. Thanks again, very helpful

It can even be quicker to use dynamic ranges so as to only calculate the
minimum necessary, such as

=SUM((OFFSET('12SP'!$A$2,0,0,COUNTA('12SP'!$A:$A)-1,1)=$A$1)*
(OFFSET('12SP'!$G$2,0,0,COUNTA('12SP'!$A:$A)-1,1)=$C$6)*
(OFFSET('12SP'!$E$2,1,0,COUNTA('12SP'!$A:$A)-1,1)<=$D$2)*
(OFFSET('12SP'!$D$2,1,0,COUNTA('12SP'!$A:$A)-1,1)))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Abas Ibrahimov" <(E-Mail Removed)> wrote in message
news:272CF8F3-63D8-49D9-AA9A-(E-Mail Removed)...
> Wow..thanks for help Max, I will try to calculate them manually..and
reduce
> the range size as much as possible. Thanks again, very helpful
>
> "Max" wrote:
>
> > One way .. I'd set the book's calc mode to Manual* (Click Tools >
Options >
> > Calculation tab, options are there). Then we could press F9 to recalc,
but
> > only as and when required, eg after all updates / changes are done.
> > *I'd usually leave "Recalc before save" unchecked (as a personal
preference)
> >
> > Another thing I might do is to use the smallest range sizes possible ..
> > > =SUM(('12SP'!$A$2:$A$65536=$A$1)*...
> > Do you really have/expect data all the way to 65K? Perhaps 1K suffices
<g>?
> > The smaller the range sizes, the faster it'll compute.
> > --
> > Max
> > Singapore
> > http://savefile.com/projects/236895
> > xdemechanik
> > ---
> > "Abas Ibrahimov" wrote:
> > > Dear friends, I`m currently using CSE formulas to extract and analyse
data
> > > with the multiple criterias. e.g:
> > >
=SUM(('12SP'!$A$2:$A$65536=$A$1)*('12SP'!$G$2:$G$65536=$C$6)*('12SP'!$E$2:$E
$65536<=$D$2)*('12SP'!$D$2:$D$65536))..But
> > > unfortunately I have a problem with sheets so that everytime I face
the
> > > calculating cells %0..%100 phrase when I make any change. Perhaps it`s
> > > because of heavy formulas..Anybody can help me in this issue?Thanks
> > > beforehand.