Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 23 May 2015 00:54:32 +0200Calc: Sum only numbers entered manually (where ISFORMULA returns FALSE)?https://ask.libreoffice.org/en/question/50970/calc-sum-only-numbers-entered-manually-where-isformula-returns-false/Hi, I have a column of cells and each cell has a number. Some of the numbers are the result of a formula and some numbers were entered manually.
Is there a way to sum only those cells whose numbers were entered manually? I've tried different combinations of `SUMIF`, `NOT`, and `ISFORMULA` but I haven't been able to get anything working.
I'm using LO v4.4.2.2.
Thanks
Fri, 22 May 2015 01:43:16 +0200https://ask.libreoffice.org/en/question/50970/calc-sum-only-numbers-entered-manually-where-isformula-returns-false/Answer by pierre-yves samyn for <p>Hi, I have a column of cells and each cell has a number. Some of the numbers are the result of a formula and some numbers were entered manually. </p>
<p>Is there a way to sum only those cells whose numbers were entered manually? I've tried different combinations of <code>SUMIF</code>, <code>NOT</code>, and <code>ISFORMULA</code> but I haven't been able to get anything working. </p>
<p>I'm using LO v4.4.2.2. </p>
<p>Thanks</p>
https://ask.libreoffice.org/en/question/50970/calc-sum-only-numbers-entered-manually-where-isformula-returns-false/?answer=50990#post-id-50990 Hi - I am afraid that FORMULA and ISFORMULA do not support matrix form. So use an additional column (which can be hidden). See the attached exampl: [IsFormulaSum.ods](/upfiles/1432281316295598.ods)Fri, 22 May 2015 09:55:41 +0200https://ask.libreoffice.org/en/question/50970/calc-sum-only-numbers-entered-manually-where-isformula-returns-false/?answer=50990#post-id-50990Comment by Lupp for <p>Hi - I am afraid that FORMULA and ISFORMULA do not support matrix form. So use an additional column (which can be hidden). See the attached exampl: <a href="/upfiles/1432281316295598.ods">IsFormulaSum.ods</a></p>
https://ask.libreoffice.org/en/question/50970/calc-sum-only-numbers-entered-manually-where-isformula-returns-false/?comment=51023#post-id-51023This seems to be a bug. I filed a report here: https://bugs.documentfoundation.org/show_bug.cgi?id=91502#c0Sat, 23 May 2015 00:54:32 +0200https://ask.libreoffice.org/en/question/50970/calc-sum-only-numbers-entered-manually-where-isformula-returns-false/?comment=51023#post-id-51023Answer by stephan66 for <p>Hi, I have a column of cells and each cell has a number. Some of the numbers are the result of a formula and some numbers were entered manually. </p>
<p>Is there a way to sum only those cells whose numbers were entered manually? I've tried different combinations of <code>SUMIF</code>, <code>NOT</code>, and <code>ISFORMULA</code> but I haven't been able to get anything working. </p>
<p>I'm using LO v4.4.2.2. </p>
<p>Thanks</p>
https://ask.libreoffice.org/en/question/50970/calc-sum-only-numbers-entered-manually-where-isformula-returns-false/?answer=50988#post-id-50988Example:
There are 1000 numbers in A1:A1000.
Fill B1:B1000 with "=ISFORMULA()" pointing to the cell to the left.
=sumif(B1:B1000;FALSE;A1:A1000) should do the trick.Fri, 22 May 2015 09:49:04 +0200https://ask.libreoffice.org/en/question/50970/calc-sum-only-numbers-entered-manually-where-isformula-returns-false/?answer=50988#post-id-50988