SUMIF oddity?

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
 
R

Ron Coderre

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
 
B

Bob Phillips

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)
 
J

Jay

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
 
Top