Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenThu, 17 Mar 2016 04:44:29 +0100Ask Your Question 0 Conditional Counting Using Multiple Columns where one condition is Not Blank and the other is Blankhttps://ask.libreoffice.org/en/question/66580/ask-your-question-0-conditional-counting-using-multiple-columns-where-one-condition-is-not-blank-and-the-other-is-blank/I have a Calc spreadsheet where two columns may or may not contain a value. If the first column contains a value, I want to count the second column if it is blank. I have been working with the COUNTIFS function, but I can't seem to get it to work. I tried =COUNTIFS(C2:C203,<>"",D2:D203,"") but I get an Err:510. I tried something like =COUNTIFS(C2:C203,COUNTA(C2:C203),D2:D203,"") but this gives 0 which is not correct. Finally, I tried =COUNTIFS(C2:C203,<>ISBLANK,D2:D203,ISBLANK) with a result of Err:510. The answer for this sheet should be 43. That is column C has 43 non blank entries where the corresponding row entry in column D is blank.Tue, 15 Mar 2016 23:00:47 +0100https://ask.libreoffice.org/en/question/66580/ask-your-question-0-conditional-counting-using-multiple-columns-where-one-condition-is-not-blank-and-the-other-is-blank/Answer by cscj01 for <p>I have a Calc spreadsheet where two columns may or may not contain a value. If the first column contains a value, I want to count the second column if it is blank. I have been working with the COUNTIFS function, but I can't seem to get it to work. I tried =COUNTIFS(C2:C203,<>"",D2:D203,"") but I get an Err:510. I tried something like =COUNTIFS(C2:C203,COUNTA(C2:C203),D2:D203,"") but this gives 0 which is not correct. Finally, I tried =COUNTIFS(C2:C203,<>ISBLANK,D2:D203,ISBLANK) with a result of Err:510. The answer for this sheet should be 43. That is column C has 43 non blank entries where the corresponding row entry in column D is blank.</p>
https://ask.libreoffice.org/en/question/66580/ask-your-question-0-conditional-counting-using-multiple-columns-where-one-condition-is-not-blank-and-the-other-is-blank/?answer=66601#post-id-66601 If I use =COUNTIFS(C2:C203;"<>";D2:D203;""), I get 0 for an answer. My data is not numeric, it is alphanumeric. Does that make a difference?Wed, 16 Mar 2016 04:18:18 +0100https://ask.libreoffice.org/en/question/66580/ask-your-question-0-conditional-counting-using-multiple-columns-where-one-condition-is-not-blank-and-the-other-is-blank/?answer=66601#post-id-66601Answer by m.a.riosv for <p>I have a Calc spreadsheet where two columns may or may not contain a value. If the first column contains a value, I want to count the second column if it is blank. I have been working with the COUNTIFS function, but I can't seem to get it to work. I tried =COUNTIFS(C2:C203,<>"",D2:D203,"") but I get an Err:510. I tried something like =COUNTIFS(C2:C203,COUNTA(C2:C203),D2:D203,"") but this gives 0 which is not correct. Finally, I tried =COUNTIFS(C2:C203,<>ISBLANK,D2:D203,ISBLANK) with a result of Err:510. The answer for this sheet should be 43. That is column C has 43 non blank entries where the corresponding row entry in column D is blank.</p>
https://ask.libreoffice.org/en/question/66580/ask-your-question-0-conditional-counting-using-multiple-columns-where-one-condition-is-not-blank-and-the-other-is-blank/?answer=66584#post-id-66584Not possible to put the comparators out of the quotes.
![image description](/upfiles/14580818072169948.png)
----------
Edited 20160316
With strings:
[CountIfs_sample.ods](/upfiles/145812687096312.ods)
![image description](/upfiles/14581268882600097.png)
Tue, 15 Mar 2016 23:44:44 +0100https://ask.libreoffice.org/en/question/66580/ask-your-question-0-conditional-counting-using-multiple-columns-where-one-condition-is-not-blank-and-the-other-is-blank/?answer=66584#post-id-66584Comment by cscj01 for <p>Not possible to put the comparators out of the quotes.</p>
<p><img alt="image description" src="/upfiles/14580818072169948.png"/></p>
<hr/>
<p>Edited 20160316</p>
<p>With strings: <br/>
<a href="/upfiles/145812687096312.ods">CountIfs_sample.ods</a></p>
<p><img alt="image description" src="/upfiles/14581268882600097.png"/></p>
https://ask.libreoffice.org/en/question/66580/ask-your-question-0-conditional-counting-using-multiple-columns-where-one-condition-is-not-blank-and-the-other-is-blank/?comment=66628#post-id-66628If I use =COUNTIFS(C2:C203;"<>";D2:D203;""), I get 0 for an answer. My data is not numeric, it is alphanumeric. Does that make a difference?Wed, 16 Mar 2016 16:43:59 +0100https://ask.libreoffice.org/en/question/66580/ask-your-question-0-conditional-counting-using-multiple-columns-where-one-condition-is-not-blank-and-the-other-is-blank/?comment=66628#post-id-66628Comment by cscj01 for <p>Not possible to put the comparators out of the quotes.</p>
<p><img alt="image description" src="/upfiles/14580818072169948.png"/></p>
<hr/>
<p>Edited 20160316</p>
<p>With strings: <br/>
<a href="/upfiles/145812687096312.ods">CountIfs_sample.ods</a></p>
<p><img alt="image description" src="/upfiles/14581268882600097.png"/></p>
https://ask.libreoffice.org/en/question/66580/ask-your-question-0-conditional-counting-using-multiple-columns-where-one-condition-is-not-blank-and-the-other-is-blank/?comment=66687#post-id-66687Thanks, that took care of it. I'm going to have to think about the function name and how it relates to the issue.Thu, 17 Mar 2016 04:44:29 +0100https://ask.libreoffice.org/en/question/66580/ask-your-question-0-conditional-counting-using-multiple-columns-where-one-condition-is-not-blank-and-the-other-is-blank/?comment=66687#post-id-66687