Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 01 Mar 2017 15:36:40 +0100How to SUMIF that tests if length of cell is 1https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/Consider the following:
![image description](/upfiles/14882796549175218.png)
What is the formula that "sums column C if the length of the corresponding cell in column A is =1" ?Tue, 28 Feb 2017 12:01:57 +0100https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/Answer by karolus for <p>Consider the following:</p>
<p><img alt="image description" src="/upfiles/14882796549175218.png"></p>
<p>What is the formula that "sums column C if the length of the corresponding cell in column A is =1" ?</p>
https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/?answer=88825#post-id-88825Hallo
Solution with the *Swiss-Army-Knife* in terms of functions:
**`=SUMPRODUCT(LEN(A1:A4)=1, C1:C4)`**
Tue, 28 Feb 2017 14:00:54 +0100https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/?answer=88825#post-id-88825Comment by Lupp for <p>Hallo </p>
<p>Solution with the <em>Swiss-Army-Knife</em> in terms of functions: <br>
<strong><code>=SUMPRODUCT(LEN(A1:A4)=1, C1:C4)</code></strong></p>
https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/?comment=88826#post-id-88826Yes. Much better imo.Tue, 28 Feb 2017 14:37:28 +0100https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/?comment=88826#post-id-88826Comment by Mike Kaganski for <p>Hallo </p>
<p>Solution with the <em>Swiss-Army-Knife</em> in terms of functions: <br>
<strong><code>=SUMPRODUCT(LEN(A1:A4)=1, C1:C4)</code></strong></p>
https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/?comment=88904#post-id-88904Agree. This may be also faster.Wed, 01 Mar 2017 09:40:37 +0100https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/?comment=88904#post-id-88904Comment by migueldealmeida for <p>Hallo </p>
<p>Solution with the <em>Swiss-Army-Knife</em> in terms of functions: <br>
<strong><code>=SUMPRODUCT(LEN(A1:A4)=1, C1:C4)</code></strong></p>
https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/?comment=88944#post-id-88944Aha! I agree, this was what I was looking for! Thanks :)Wed, 01 Mar 2017 15:36:40 +0100https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/?comment=88944#post-id-88944Answer by Mike Kaganski for <p>Consider the following:</p>
<p><img alt="image description" src="/upfiles/14882796549175218.png"></p>
<p>What is the formula that "sums column C if the length of the corresponding cell in column A is =1" ?</p>
https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/?answer=88813#post-id-88813If you use wildcards in formulas (default for recent LO versions), then you may use `"=?"` as criteria. If you use regular expressions, your criteria will be `"=."`. See `Tools`->`Options`->`LibreOffice Calc`->`Calculate` for settings that control using regexes/wildcards in formulas.Tue, 28 Feb 2017 12:26:12 +0100https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/?answer=88813#post-id-88813Comment by erAck for <p>If you use wildcards in formulas (default for recent LO versions), then you may use <code>"=?"</code> as criteria. If you use regular expressions, your criteria will be <code>"=."</code>. See <code>Tools</code>-><code>Options</code>-><code>LibreOffice Calc</code>-><code>Calculate</code> for settings that control using regexes/wildcards in formulas.</p>
https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/?comment=88819#post-id-88819Note that `"=?"` depends on the Tools-Options-Calc-Calculate setting "Search criteria = and <> must apply to whole cells", if that is disabled the search matches any cell content of at least one character, not just one character.Tue, 28 Feb 2017 13:04:59 +0100https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/?comment=88819#post-id-88819Comment by migueldealmeida for <p>If you use wildcards in formulas (default for recent LO versions), then you may use <code>"=?"</code> as criteria. If you use regular expressions, your criteria will be <code>"=."</code>. See <code>Tools</code>-><code>Options</code>-><code>LibreOffice Calc</code>-><code>Calculate</code> for settings that control using regexes/wildcards in formulas.</p>
https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/?comment=88816#post-id-88816I have wildcards on. Not sure how to write it. Do you mean =SUMIF(A1:A4,LEN(=?)=1,C1:C4) ? This formula doesn't work.Tue, 28 Feb 2017 12:36:48 +0100https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/?comment=88816#post-id-88816Comment by Mike Kaganski for <p>If you use wildcards in formulas (default for recent LO versions), then you may use <code>"=?"</code> as criteria. If you use regular expressions, your criteria will be <code>"=."</code>. See <code>Tools</code>-><code>Options</code>-><code>LibreOffice Calc</code>-><code>Calculate</code> for settings that control using regexes/wildcards in formulas.</p>
https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/?comment=88817#post-id-88817=SUMIF(A1:A4,"=?",C1:C4)Tue, 28 Feb 2017 12:37:39 +0100https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/?comment=88817#post-id-88817Comment by karolus for <p>If you use wildcards in formulas (default for recent LO versions), then you may use <code>"=?"</code> as criteria. If you use regular expressions, your criteria will be <code>"=."</code>. See <code>Tools</code>-><code>Options</code>-><code>LibreOffice Calc</code>-><code>Calculate</code> for settings that control using regexes/wildcards in formulas.</p>
https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/?comment=88823#post-id-88823The **`=`** inside the criteria-expression is **optional** both (wildcard|regEx) works without it.
anyway I would prefer a solution without dependencies of weird settingsā¦see my AnswerTue, 28 Feb 2017 13:53:16 +0100https://ask.libreoffice.org/en/question/88810/how-to-sumif-that-tests-if-length-of-cell-is-1/?comment=88823#post-id-88823