Description: Swedish currency symbol is a colon : so local setting for formulae separators is set to ; semicolon. Language interface is set to English - UK When an INDIRECT() reference is defined within the SUM() function with the ; separator it simply returns the individual value for the indirectly referenced cell. If the : separator is used it returns the correct summation. Simple spreadsheet "Indirect" attached to demonstrate. It is an assumption that the error is within INDIRECT() as the chosen separator performs correctly with all other SUM()functions Steps to Reproduce: In the attached sheet "Indirect" Type any number between 1 & 18 in B1 C1 will create a target cell reference for column D E1 contains the formula to SUM() D1>target using the semicolon ; separator F1 contains the formula to SUM() D1>target using the colon : separator Only one of them is correct Actual Results: E1 contains an incorrect result F1 contains a correct result Expected Results: As I had defined the separator to suit my local currency symbols I would expect E1 to show the correct result but I am uncertain whether I should expect an error message (502 or 504?) in F1. Reproducible: Always User Profile Reset: No OpenGL enabled: Yes Additional Info: Version: 6.3.6.2 (x64) Build ID: 2196df99b074d8a661f4036fca8fa0cbfa33a497 CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; Locale: sv-SE (en_GB); UI-Language: en-GB Calc: threaded It may be one of those very esoteric issues that I'm now fully aware of and obviously can make allowances to ensure the correct result. The example is very simplistic, I am using the generated INDIRECT() reference in a subsequent VLOOKUP()to define the parameters of a SUM()function. The simple example is to prove it was the function separator misbehaving and nothing wrong with my own syntax or sequences. I have no perception of whether others are aware of the malfunction and simple workaround, have never tried a similar construct, or have just abandoned the effort in favour of a "blunt instrument" approach-
Created attachment 165335 [details] Simple example of INDIRECT() Function
0. the Swedish currency symbol certainly is not ':' colon but 'kr' for Svensk krona instead. If the function parameter separator is ';' semicolon then the formula =SUM(D1;INDIRECT(C1)) returns the sum of two individual arguments, namely D1 and the reference returned by the call to INDIRECT(C1). In the formula =SUM(D1:INDIRECT(C1)) the ':' colon is the range operator so the range passed to sum is the result of D1 as one corner and the second corner is the reference returned by the call to INDIRECT(C1). So if C1 contains "D7" the resulting range is D1:D7 passed to SUM(). There is no bug and not an esoteric issue, the function parameter separator is not misbehaving and there is no malfunction. It is simply that you were not aware of the syntax.
(In reply to Eike Rathke from comment #2) > 0. the Swedish currency symbol certainly is not ':' colon but 'kr' for > Svensk krona instead. > > If the function parameter separator is ';' semicolon then the formula > =SUM(D1;INDIRECT(C1)) > returns the sum of two individual arguments, namely D1 and the reference > returned by the call to INDIRECT(C1). > > In the formula > =SUM(D1:INDIRECT(C1)) > the ':' colon is the range operator so the range passed to sum is the result > of D1 as one corner and the second corner is the reference returned by the > call to INDIRECT(C1). So if C1 contains "D7" the resulting range is D1:D7 > passed to SUM(). > > There is no bug and not an esoteric issue, the function parameter separator > is not misbehaving and there is no malfunction. It is simply that you were > not aware of the syntax. Hi Eike, I inadvertently referred to the currency separator as the currency symbol. The currency symbol is in fact the two character ISO country code for Sverige which is "SE" plus the single character currency initial "K" hence "SEK". The currency separator for numerical representations of the value is the colon with two potential presentations - 123:25 when there are fractional portions and 123:- when it is a simple integer. The fractional representation is sometimes also supplemented with "k" or "kr". As we have three local Kronor to contend with (Danska, Norska och Svenska)monetary represetations become slightly complicated. Something has definitely gone wrong. Further experiments have identified that if I use the "drag select" and Sigma [sum] function or alternatively the Sigma [sum] function in an empty cell adjacent to "populated" data cells then the system automatically writes two different formulae - one each with the colon and semicolon separators. It also produces inconsistency when some of the selected or inferred cells contain the results of their own formulae. My assumption that it was the INDIRECT() function was indeed erroneous. I will endeavour to clearly define the inconsistencies and submit a new report