Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 11 Sep 2019 23:05:13 +0200Sum of formulated range not working.https://ask.libreoffice.org/en/question/208186/sum-of-formulated-range-not-working/I am trying to sum a range where each cell in the range is the result of a formula, but for some reason it doesn't work, the sum is always zero. I have created a simplified reproducible case. See attached image and spreadsheet demonstrating the problem. Any ideas?
[C:\fakepath\SumOfFormulatedRangeIssue.png](/upfiles/1568165170799703.png)
[C:\fakepath\SUM OF FORMULATED RANGE PROBLEM.ods](/upfiles/15681653044114145.ods)Wed, 11 Sep 2019 03:32:42 +0200https://ask.libreoffice.org/en/question/208186/sum-of-formulated-range-not-working/Answer by Ratslinger for <p>I am trying to sum a range where each cell in the range is the result of a formula, but for some reason it doesn't work, the sum is always zero. I have created a simplified reproducible case. See attached image and spreadsheet demonstrating the problem. Any ideas?</p>
<p><a href="/upfiles/1568165170799703.png">C:\fakepath\SumOfFormulatedRangeIssue.png</a></p>
<p><a href="/upfiles/15681653044114145.ods">C:\fakepath\SUM OF FORMULATED RANGE PROBLEM.ods</a></p>
https://ask.libreoffice.org/en/question/208186/sum-of-formulated-range-not-working/?answer=208187#post-id-208187Hello,
There should be no quotes surrounding the `1` in the formulas:
=IF(B4="Y", "1", 0)
should be:
=IF(B4="Y", 1, 0)Wed, 11 Sep 2019 03:45:09 +0200https://ask.libreoffice.org/en/question/208186/sum-of-formulated-range-not-working/?answer=208187#post-id-208187Comment by SteveLFC for <p>Hello,</p>
<p>There should be no quotes surrounding the <code>1</code> in the formulas:</p>
<pre><code>=IF(B4="Y", "1", 0)
</code></pre>
<p>should be:</p>
<pre><code>=IF(B4="Y", 1, 0)
</code></pre>
https://ask.libreoffice.org/en/question/208186/sum-of-formulated-range-not-working/?comment=208227#post-id-208227Thank you for your answer. For your information the title I copied and pasted in a hurry from the spreadsheet which is capitalised, just a simple matter I overlooked but then in my world a title being capitalised or emboldenned is commonplace since its a title, its not intended to mean anything else, so you shouldn't be so quick jump to conclusions.Wed, 11 Sep 2019 12:27:25 +0200https://ask.libreoffice.org/en/question/208186/sum-of-formulated-range-not-working/?comment=208227#post-id-208227Comment by SteveLFC for <p>Hello,</p>
<p>There should be no quotes surrounding the <code>1</code> in the formulas:</p>
<pre><code>=IF(B4="Y", "1", 0)
</code></pre>
<p>should be:</p>
<pre><code>=IF(B4="Y", 1, 0)
</code></pre>
https://ask.libreoffice.org/en/question/208186/sum-of-formulated-range-not-working/?comment=208312#post-id-208312No worries. I cant believe I even asked this question, its so obvious, I cant believe I didn't notice the quotes, clearly Ive been working too long and too late :)Wed, 11 Sep 2019 22:47:29 +0200https://ask.libreoffice.org/en/question/208186/sum-of-formulated-range-not-working/?comment=208312#post-id-208312Comment by m.a.riosv for <p>Hello,</p>
<p>There should be no quotes surrounding the <code>1</code> in the formulas:</p>
<pre><code>=IF(B4="Y", "1", 0)
</code></pre>
<p>should be:</p>
<pre><code>=IF(B4="Y", 1, 0)
</code></pre>
https://ask.libreoffice.org/en/question/208186/sum-of-formulated-range-not-working/?comment=208313#post-id-208313Please, if the answer solves the question click ✔.Wed, 11 Sep 2019 23:05:13 +0200https://ask.libreoffice.org/en/question/208186/sum-of-formulated-range-not-working/?comment=208313#post-id-208313Comment by Ratslinger for <p>Hello,</p>
<p>There should be no quotes surrounding the <code>1</code> in the formulas:</p>
<pre><code>=IF(B4="Y", "1", 0)
</code></pre>
<p>should be:</p>
<pre><code>=IF(B4="Y", 1, 0)
</code></pre>
https://ask.libreoffice.org/en/question/208186/sum-of-formulated-range-not-working/?comment=208279#post-id-208279@SteveLFC,
You are correct. I should have only stated to "Please not use all caps in your question as this is taken by most as yelling".Wed, 11 Sep 2019 18:20:01 +0200https://ask.libreoffice.org/en/question/208186/sum-of-formulated-range-not-working/?comment=208279#post-id-208279Comment by Ratslinger for <p>Hello,</p>
<p>There should be no quotes surrounding the <code>1</code> in the formulas:</p>
<pre><code>=IF(B4="Y", "1", 0)
</code></pre>
<p>should be:</p>
<pre><code>=IF(B4="Y", 1, 0)
</code></pre>
https://ask.libreoffice.org/en/question/208186/sum-of-formulated-range-not-working/?comment=208188#post-id-208188P.S. There is not need to yell out your question - ALL CAPS.Wed, 11 Sep 2019 03:49:03 +0200https://ask.libreoffice.org/en/question/208186/sum-of-formulated-range-not-working/?comment=208188#post-id-208188