Conditional sum

shouldn't it be
=SUMIF($B$1:$B$15,"<="&B1,$A$1:$A$15)

Hi Martin, patphilly
This is something i recently came across (in another forum)
the "range" dimensions ($B$1:$B$5 in the example) actually determine the "sum_range "dimensions while the first cell reference of the "sum_range" ($A$1 in the example) determines the upperleft startingpoint of the "sum_range".
e.g. if we write the formula as : =sumif($B$1:$B$5,"<="&B1,$A$1) or
=sumif($B$1:$B$5,"<="&B1,$A$1:$A$5) or
=sumif($B$1:$B$5,"<="&B1,$A$1:$A$2) the result should be the same.
If i understood correctly.... big if!
As for the results not updating if you change A2 to 310: this does work for me.

but it doesnt recalculate???? unless you specify the whole range

hmm not in my workbook!

<=B1 or =b1 or any variant thereof cant be used in countif it requires either cell ref OR a text string
but if you use & to combine the text ref "<=" with the cell ref B1 then it will accept it

I don't have an answer to this but thought I would simply state that like WHER I can not replicate this issue...

I've tested on both 2007 (Vista SP2) & 2002 (XP SP3) and in both cases calculation updates with change in A2 ... so even though the precedents are not directly referenced the formula still recalculates as expected.

I note OP has Mac and thus I can't vouch for that version - I know note from a quick google that there have been issues with SUMIF sum_range & Mac on earlier versions (98) - but this would not account for the issues experienced by Martin ...

Just to clarify regards environments, on which of your version(s) did you test Martin ?

Bugs on specific versions are not unheard of... we came across one recently regards use of double unary coercion which when used in conjunction with Arrays/Sumproduct with Volatile precedents causes the auto calculation of said functions to fail on Volatile action... this bug is specific to XL2007 (resolved in 2010 it seems)

Originally Posted by WHER

This is something i recently came across (in another forum)
the "range" dimensions ($B$1:$B$5 in the example) actually determine the "sum_range "dimensions while the first cell reference of the "sum_range" ($A$1 in the example) determines the upperleft startingpoint of the "sum_range".
e.g. if we write the formula as : =sumif($B$1:$B$5,"<="&B1,$A$1) or
=sumif($B$1:$B$5,"<="&B1,$A$1:$A$5) or
=sumif($B$1:$B$5,"<="&B1,$A$1:$A$2) the result should be the same.
If i understood correctly.... big if!

the above is (as you would expect) perfectly correct, however, irrespective of the above issue I'd not use the top left only approach simply for the sake of transparency (Formula Auditing / Tracing Precedents)

I am intrigued and will keep digging... until such time as we're put out of our collective misery by another of course !

D.O

EDIT: - adding link to XL00 article: http://support.microsoft.com/kb/215732

Follow up...

Thought I'd look at this again given the experience outlined by Martin & OP would to me at least seem to be "expected" ... ie given the altered cell is not explicitly referenced as a precedent one would not expect the formula to recalculate.... unless it was Volatile.

Using the most basic of tests ...

If we simplify further and create a file:

A1:A4:
a, a, b, a

B1:B4
1,2,3,4

C1:
=SUMIF(A1:A4,"a",B1:B4)

and save the file, close, reopen & close we are not prompted to save.

If we re-open and subsequently add

C2:
=SUMIF(A1:A4,"a",B1)

and save the file, close, reopen & close we are now prompted to save...

The "save" prompt when no physical changes have been made by the user is seen as a very basic (potential) indicator of volatility... it's by no means watertight as proven by INDEX which does the same though not volatile (ie marks cell as "dirty" hence prompt)
To do more thorough testing would require more sophisticated tools but thought I'd highlight above in case anyone else was curious... I will look again tomorrow in more depth.

Given the link I added via Edit to my prior post just now it would "seem" that up to and incl. XL2000 if the altered cell sat outside of the referenced sum_range but was included by virtue of the criteria_range dimensions the SUMIF itself would not recalculate ... however post XL2000 the implication would be that such SUMIF constructs become Volatile which I was never aware of.

Again - this is all still somewhat speculative... hopefully there will be a more definitive answer to this somewhere.


(@ WHER - yep I know - wasn't implying you were advocating in preference to the "standard" )

i use 97 as a working thing at home, 2003 at work and 2007 for 2007 things at home as i have to spark up my virtual pc to use it. so looks like 97 may be the issue. thanks for looking in donkeyote.

Martin, FYI, both Colin Legg & Charles Williams have confirmed this in XL2002, XL2003, XL2007... I don't have 2010 to test myself.
Romperstomper is going to investigate the Mac side of things for us and will hopefully post here in the not too distant future with his findings.

Up to and incl. 2000 we know this lack of recalculation is "by design" and not a bug etc...

Anyway, all in all, just another good reason to be sure you reference the sum_range as fully as possible

It's "official": http://www.decisionmodels.com/calcsecretsi.htm

thumbs up to EF.com for this one I think... wouldn't say we discovered it but at least this undocumented feature is now "out there" ...

I've tested in 2004 and 2008 and neither one recalculates if you use the one-cell sum range and change a cell that 'ought' to be in the implicit sum range.