how come this = 0 and not #REF or something?
=SUMIFS(AccQTYMT,AccProduct,F$56,AccTradeType,F$57,AccStrategy,#REF!,AccMaturity,$D104)
=SUM(G8,#REF!) = #REF! as expected
=SUMIF(E7:E12,#REF!,F7:F12) = 0 too.
All in 2007 (and 2010), I don’t have a 2003 to check just now, is it the same? (just the SUMIF obviously, you dinosaurs don’t have the luxury of SUMIFS ;-))
OpenOffice returns the #REF! I would expect.
Would you expect a formula to return an error if one of its required arguments is an error?
In fairness if there are #REF!s in the data it matches them and returns the total, so in a literal sense it ‘works’, but I’m not sure its what I would expect. What about you?
cheers
Simon
Wednesday, 2nd June, 2010 at 8:13 am |
Gives 0 in 2003 as well
Wednesday, 2nd June, 2010 at 9:05 am |
Depends on what you understand errors to represent. If you see them working in the same way as nulls in SQL, i.e. unknown where x=unknown always evaluates to unknown, then the 0 is indeed unexpected. However if you see them as values, then they can be compared with other values and the 0 makes perfect sense.
Wednesday, 2nd June, 2010 at 10:39 am |
I’d expect them to propogate like they do in most other cases (and on OO calc), so they can be traced and fixed.
Wednesday, 2nd June, 2010 at 1:32 pm |
COUNTIF, DCOUNT, DSUM all treat the #REF! as a value to be matched.
MATCH & LOOKUP propagate the error.
That’s Excel’s way, I guess :-)
Another one for the cabinet of curiosities.
Wednesday, 2nd June, 2010 at 1:55 pm |
Yes this is an inconsistency IMHO. It does mean that it would be possible to have a missing reference value in your total and that is not a good thing.
It defininitely will make it hard to completely trust these formulae as the error will not be visible unless you select the cell and look at the refernce itself.
Unfortunate but it’s the way it is and we have to watch out for it I guess.
OTOH MS could fix this in a Service pack because there would be no argument for leaving it this way to protect legacy spreadsheets (??)
Better send it on to the Excel Team, but I’m not sure to who as our friend Dave G isn’t there anymore :-(
Dick
Wednesday, 2nd June, 2010 at 2:36 pm |
This works the same in all versions which have SUMIF. It only works when error values are criteria, and that may be because criteria arguments are always handled as text strings. That is, Excel always and automtically converts the #REF! in the criteria arguments into “#REF!” (or maybe “=#REF!”).
FAR WORSE, criteria beginning with don’t always produce the complementary result to criteria otherwise the same but beginning with =. For example, enter 1 in A1, =”1″ in A2, 5 in B1 and 66 in B2. Then compare the formulas
=SUMIF(A1:A2,1,B1:B2)
=SUMIF(A1:A2,”1″,B1:B2)
=SUMIF(A1:A2,”1″,B1:B2)
=SUMIF(A1:A2,”1*”,B1:B2)
How many people would you think would be caught short by
=SUMIF(A,”x”,B)
not returning the same value as
=SUM(B)-SUMIF(A,”x”,B)
?
The problem is that Excel treats = criteria as matching both numbers and text when the criteria is numeric, but reverts to standard Excel semantics and distinguishes between text and numbers in criteria.
This is old news. Poor design, but at this point I don’t see this changing.
Wednesday, 2nd June, 2010 at 2:41 pm |
That’s ‘criteria beginning with <>’.
Wednesday, 2nd June, 2010 at 2:44 pm |
And the 3rd through 5th formulas should be
=SUMIF(A1:A2,”<>1″,B1:B2)
=SUMIF(A1:A2,”<>1*”,B1:B2)
=SUMIF(A,”<>x”,B)
Tuesday, 8th June, 2010 at 8:28 am |
There are other such inconsistencies
A classic example is a the way in which a cell containing a single quote is treated by different features and formulas
a) Autofilters , Find, CountBlank, Sumproduct/CountIF(s) treats the Cell as Blank
b) Advanced Filters ,Sort, CountA,ISBLANK GoTo Special Blanks doesn’t
Tuesday, 8th June, 2010 at 3:38 pm |
@sam – differences between SUMPRODUCT and COUNTIF need to be expressed precisely. SUMPRODUCT(–(x=”")) and COUNTIF(x,”") will return the same result, but SUMPRODUCT(–ISBLANK(x)) can return a different result. A single quote should be treated as identical to “”.
Tuesday, 8th June, 2010 at 5:00 pm |
@Harlan,
You are right of course….I should have made that more clear.
I am amazed at how things like this have continued uncorrected across versions….
Tuesday, 8th June, 2010 at 6:24 pm |
Backwards compatibility. Workarounds needed in one version should continue to work in subsequent versions.
But the single quote is the syntactic means of entering a zero-length string constant. While it’s possible to generate a zero-length string constant by entering the formula =”", copying it, then pasting it in-place as a value, such a cell will APPEAR identical to a blank cell in the formula bar, but it won’t be a blank cell. To me that’s far more questionable than treating a single quote as equivalent to “”.