Question about SUMIFS(A:A,...)

  • Thread starter Thread starter JoeU2004
  • Start date Start date
J

JoeU2004

I have a question about variable ranges like A:A compared to fixed ranges
like A1:A18000. I asked the question in another thread. But I suspect it
got lost in the "noise".

I don't think my question is specific to SUMIFS. But to illustrate....
Another advantage is that SUMIFS only calculates based
on the used range.

There is no difference in efficiency between these formulas:
=SUMIFS(E:E,A:A,"w",B:B,"x",C:C,"y",D:D,"z")
=SUMIFS(E1:E18000,A1:A18000,"w",B1:B18000,"x",C1:C18000,"y",D1:D18000,"z")
Click to expand...

I think Biff meant to write "there may be a significant difference in
efficiency", to dovetail with his comment that "SUMIFS only calculates based
on the used range".

(Right, Biff?)

I presume that is based on the assumption that some range property (I forget
which one) demarcates the last-used cell in a column. Right?

Is that demarcation always correct?

In Excel 2003, I notice that when I put a value or formula in some distance
cell (e.g. AI65000), then delete it, ctrl+End still goes to that distance
cell :-(.

Does that also screw up the last-used cell demarcation in that column? For
example, would that cause the range AI:AI to be processed as AI1:AI65000,
for example? In Excel 2007 as well as Excel 2003?
 
Well, assuming the available range is 1 - 18000, then there will be no
difference, as SUMIFS will use only the data available.

As you state, if a cell well beyond the normal data set is used, then
additional memory will be required.

However, if ths cell is deleted, along with all other rows between our table
and this prodigal cell, then file saved, we revert back to an accurate end of
sheet reference.

This appears to be the case in both 2003 and 2007.
 
Thanks.


----- original message -----

Sean Timmons said:
Well, assuming the available range is 1 - 18000, then there will be no
difference, as SUMIFS will use only the data available.

As you state, if a cell well beyond the normal data set is used, then
additional memory will be required.

However, if ths cell is deleted, along with all other rows between our
table
and this prodigal cell, then file saved, we revert back to an accurate end
of
sheet reference.

This appears to be the case in both 2003 and 2007.
Click to expand...
 
There is no difference in efficiency between these formulas:
I think Biff meant to write "there may be a significant difference in
efficiency", to dovetail with his comment that "SUMIFS only calculates
based on the used range".
(Right, Biff?)
Click to expand...

No, I meant:

There is no difference in efficiency between those formulas as was
originally described in that other post.

If the all the data is entered in the range A1:E18000 then Excel establishes
that as the used range. So:

=SUMIFS(E:E,A:A,"w",B:B,"x",C:C,"y",D:D,"z")

Only calculates cells A1:E18000. So there is no difference in the efficiency
when the used range is A:E18000 whether you use:

=SUMIFS(E1:E18000,A1:A18000,"w",B1:B18000,"x",C1:C18000,"y",D1:D18000,"z")

Or:

=SUMIFS(E:E,A:A,"w",B:B,"x",C:C,"y",D:D,"z")

Calc times will be the same.

*However*, if you add additional data beyond A:E18000 then a *new* used
range is established. So, yes, if you add new data down to say, A:E100000
and then delete that data the used range is still set to A:E100000 and this
formula will calculate to that new used range:

=SUMIFS(E:E,A:A,"w",B:B,"x",C:C,"y",D:D,"z")

While this formula will only calculate to the specific range:

=SUMIFS(E1:E18000,A1:A18000,"w",B1:B18000,"x",C1:C18000,"y",D1:D18000,"z")

We see used range related issues in the ngs occasionally in the questions:
why are my scroll bars so sensitive? Or, why is my file size so big with
such little data?

It's because the used range is much larger that the actual range that
contains data.

If you find yourself adding/deleting big chunks of data often then it's a
good idea, as part of regular file maintenance, to reset the used range.
It's a very easy process.

Referring back to the original topic of these threads:

Which is more efficient, SUMIFS or SUMPRODUCT?

SUMPRODUCT and other array processing functions/formulas will calculate
*every* cell referenced in the formulas regardless of the used range. The
new SUMIFS function does process arrays but it's also "bound" to the used
range. I think MS used the original code for the SUMIF function and just
tweaked it to work with arrays. (just my guess)
 
Back
Top