Thanks in advance for help.
Here is what I am trying to do.
If cells D2 through M2 are all empty then leave designated cell empty regardless of other circumstances. If cells D2 through M2 are not empty and the SUM of cells D2 through M2 is zero, then enter “OK”. If cells D2 through M2 are not empty and the SUM of cells D2 through M2 is not zero, then enter “Error”.
When I enter
=IF(D2:M2="","",IF(SUM(D2:M2)=0,“OK’;“error””))
I get Err:508
Help is appreciated
Use CONCAT to join the cells to check if all are empty. Also you have one single ’ instead of " after OK, and two “” instead of " after error.
=IF(COUNTA(D2:M2)=0,"",IF(SUM(D2:M2)=0,"OK","error"))
or if only numbers are to be considered:-
=IF(COUNT(D2:M2)=0,"",IF(SUM(D2:M2)=0,"OK","error"))
You also have a mix of “,” and “;” for argument separators. Depending on your language settings you might have to use “;”.
Edit. Note if the cells contain text the sum might still equal zero and display OK.
Edited above to remove CONCAT function, as @Lupp pointed out that is only a recent addition and will not work in older versions of LibreOffice. Also probably a bad way to check all cells are blank.
The CONCAT function is very fresh: Only implemented in V5.2 (and higjher) and under discussion in the bugzilla site.
The semicolon is specified to be accepted in all the locales. Only the comma may cause problems in those locales using it as the decimal separator. In “anglophile” locales, however, the entered semicolons will automatically be converted. (This was introduced not too long ago for bad reasons.)
That worked great.
Thanks for your help.
I removed CONCAT, not only because its recently added but also it seemed to be a misuse of this function.
I removed CONCAT, not only because its recently added but also it seemed to be a misuse of this function.
Err:508 should mean "Parentheses not paired correctly. I cannot see this error in your formula.
However, it is syntactically erroneous in more than one points: There is a single apostrophe in one place where a qoutation mark should be and in another place there are two quotes where one should be. In addition I would recommend to consistently use the semicolon as parameter delimiter.
The more critical error is a semantic one. =IF(D2:M2=""; ...;...)is obviously meant as the condition “All the cells in the range D2:M2 are blank or have the empty string as result.” You wrongly assume that an equation between an array (D2:M2) and a scalar ("") will perform an implicit conjunction (AND). This is not the case. The evaluator will try to pick an element of the array in “intersection mode” and, depending on the position of the expression (In what cell?), it will most probably fail.
To get the condition correct you may put AND(D2:M2="") in the position of the condition and enter the formula for evaluation in array mode (Ctrl+Shift+Enter). To avoid the explicit array mode you may use SUMPRODUCT(D2:M2="")=COLUMNS(D2:M2) or COUNTIF(D2:M2;"")=COLUMNS(D2:M2) or probably (if “not empty” actually is meant as “containing a number” and just one number in the range is sufficient (OR / disjunction instead of AND) COUNT(D2:M2) > 0would do. (Otherwise COUNT(D2:M2)=COLUMNS(D2:M2) again.)
I think you may have inverted the logic of the first condition. COUNTIF(D2:M2;"<>")=0 or COUNTA(D2:M2)=0 would fit the original intention, or COUNT(D2:M2)=0 if only numbers are to be considered.
Appreciate your input.
Thanks