SUMIF oddity?

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

Can I ask a question about the third argumnet in SUMIF, the sum_range.

If I just enter a single cell-ref for the sum_range the formula still works
as though I have defined a full range. Excel seems to take the cell-ref I
input as the starting point of a range the same size/shape as the
criteria_range.

Is this an intended behaviour? or some weird oddity?

And does it have any useful applications over just defining the full range?

Many thanks,

Jason
 
I'm not sure if SUMIF's behavior was intended or not....but, I consider it a
nice keystroke-saving feature. Since there is a one-to-one relationship
between the test cells and the sum cells, it makes sense that the 2 ranges
must be the same size. SUMIF extends the 1-cell third argument in the same
direction as the 1st range (horizontal or vertical).

Oddly, LOOKUP seems to have similar, but different behavior. If it's second
argument is a vertical range and you only enter a single cell address for the
third argument....it assumes that range is horizontal! However, if you supply
a 2-cell vertical range for the third argument....it implicitly extends that
range downward.

I hope that helps.
***********
Regards,
Ron

XL2003, WinXP
 
Jason,

There is one very serious problem with this technique.

Let's take data of

....| A| B| C|
..1| | x| 1|
..2| | y| 2|
..3| | x| 3|
..4| | y| 4|
..5| | x| 5|
..6| | x| 6|
..7| | x| 7|

and a formula of

=SUMIF(B1:B7,"x",C1)

On first input, this correctly returns 22.

However, on Excel 2000, if you change C7 to a value of 8, the formula does
not update. I assume that this is because C7 is not referenced in the
formula, so Excel's dependency algorithm doesn't kick in to force a
recalculation.

This had changed in Excel 2002/XP, where it automatically updates.
Presumably, this means that SUMIF has become a volatile function in 2002/XP?
Or maybe it internally stores the extended range. My testing suggests that
it is now volatile.

So, using a technique that behaves differently in different versions of
Excel is not a good idea IMO.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Bob said:
Jason,

There is one very serious problem with this technique.

Let's take data of

...| A| B| C|
.1| | x| 1|
.2| | y| 2|
.3| | x| 3|
.4| | y| 4|
.5| | x| 5|
.6| | x| 6|
.7| | x| 7|

and a formula of

=SUMIF(B1:B7,"x",C1)

On first input, this correctly returns 22.

However, on Excel 2000, if you change C7 to a value of 8, the formula does
not update. I assume that this is because C7 is not referenced in the
formula, so Excel's dependency algorithm doesn't kick in to force a
recalculation.

This had changed in Excel 2002/XP, where it automatically updates.
Presumably, this means that SUMIF has become a volatile function in 2002/XP?
Or maybe it internally stores the extended range. My testing suggests that
it is now volatile.

So, using a technique that behaves differently in different versions of
Excel is not a good idea IMO.
Click to expand...
Thanks Bob,

As usual a succint, cogent replmost appreciated.

Dest Regards.........Jason
 
Back
Top