Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 14 Mar 2014 09:43:36 +0100How do I count how many text entries in column A match the entry in column Bhttps://ask.libreoffice.org/en/question/31208/how-do-i-count-how-many-text-entries-in-column-a-match-the-entry-in-column-b/I've tried constructs like COUNTIF(A1:A5, EXACT(A1,B1)) but nothing seems to work...
Update 1: I've added an example:
| A | B |
| X | X | --> 1
| X | Y |
| Y | Y | --> 2
| Y | Z |
| Z | Z | --> 3
Here, it should return 3
Thu, 13 Mar 2014 17:01:46 +0100https://ask.libreoffice.org/en/question/31208/how-do-i-count-how-many-text-entries-in-column-a-match-the-entry-in-column-b/Answer by kumiponi for <p>I've tried constructs like COUNTIF(A1:A5, EXACT(A1,B1)) but nothing seems to work...</p>
<p>Update 1: I've added an example:</p>
<pre><code>| A | B |
| X | X | --> 1
| X | Y |
| Y | Y | --> 2
| Y | Z |
| Z | Z | --> 3
</code></pre>
<p>Here, it should return 3</p>
https://ask.libreoffice.org/en/question/31208/how-do-i-count-how-many-text-entries-in-column-a-match-the-entry-in-column-b/?answer=31230#post-id-31230I understood that the asker has two columns and wants to find how many rows have matching entries, between those columns. If column A contains, say, potato, turnip, and carrot, and column B contains potato, broccoli, and carrot, then you'd want the value 2.
The normal thing to do in this situation is to create a third column that has a comparison formula on every row, and then count the matches. You first enter =IF(A1=B1;1;0) into C1. Then you select the cells you need in column C and use Edit > Fill > Down. This copies the formula and changes it to suit every row. Then you put something like =SUM(C1:C100) somewhere to count how many rows match. You can hide the third column if you think it looks messy.
Another way to do this, without the extra column, would be to write a macro function. Depending on how versatile you'd want the function to be, it could be quite short or tens of lines long.Fri, 14 Mar 2014 00:52:21 +0100https://ask.libreoffice.org/en/question/31208/how-do-i-count-how-many-text-entries-in-column-a-match-the-entry-in-column-b/?answer=31230#post-id-31230Comment by kumiponi for <p>I understood that the asker has two columns and wants to find how many rows have matching entries, between those columns. If column A contains, say, potato, turnip, and carrot, and column B contains potato, broccoli, and carrot, then you'd want the value 2.</p>
<p>The normal thing to do in this situation is to create a third column that has a comparison formula on every row, and then count the matches. You first enter =IF(A1=B1;1;0) into C1. Then you select the cells you need in column C and use Edit > Fill > Down. This copies the formula and changes it to suit every row. Then you put something like =SUM(C1:C100) somewhere to count how many rows match. You can hide the third column if you think it looks messy.</p>
<p>Another way to do this, without the extra column, would be to write a macro function. Depending on how versatile you'd want the function to be, it could be quite short or tens of lines long.</p>
https://ask.libreoffice.org/en/question/31208/how-do-i-count-how-many-text-entries-in-column-a-match-the-entry-in-column-b/?comment=31232#post-id-31232Extra tip: if you want more powerful tools to manipulate that third column I mentioned, you may want to learn about [array functions](https://help.libreoffice.org/Calc/Array_Functions).Fri, 14 Mar 2014 01:01:07 +0100https://ask.libreoffice.org/en/question/31208/how-do-i-count-how-many-text-entries-in-column-a-match-the-entry-in-column-b/?comment=31232#post-id-31232Comment by Michael Clerx for <p>I understood that the asker has two columns and wants to find how many rows have matching entries, between those columns. If column A contains, say, potato, turnip, and carrot, and column B contains potato, broccoli, and carrot, then you'd want the value 2.</p>
<p>The normal thing to do in this situation is to create a third column that has a comparison formula on every row, and then count the matches. You first enter =IF(A1=B1;1;0) into C1. Then you select the cells you need in column C and use Edit > Fill > Down. This copies the formula and changes it to suit every row. Then you put something like =SUM(C1:C100) somewhere to count how many rows match. You can hide the third column if you think it looks messy.</p>
<p>Another way to do this, without the extra column, would be to write a macro function. Depending on how versatile you'd want the function to be, it could be quite short or tens of lines long.</p>
https://ask.libreoffice.org/en/question/31208/how-do-i-count-how-many-text-entries-in-column-a-match-the-entry-in-column-b/?comment=31249#post-id-31249Thanks! I was hoping to avoid extra columns though, it seemed a simple enough request :-/ For example, conditional formatting can compare columns this way and highlight the mismatches, so I figured counting would be doable to...Fri, 14 Mar 2014 09:28:31 +0100https://ask.libreoffice.org/en/question/31208/how-do-i-count-how-many-text-entries-in-column-a-match-the-entry-in-column-b/?comment=31249#post-id-31249Answer by Michael Clerx for <p>I've tried constructs like COUNTIF(A1:A5, EXACT(A1,B1)) but nothing seems to work...</p>
<p>Update 1: I've added an example:</p>
<pre><code>| A | B |
| X | X | --> 1
| X | Y |
| Y | Y | --> 2
| Y | Z |
| Z | Z | --> 3
</code></pre>
<p>Here, it should return 3</p>
https://ask.libreoffice.org/en/question/31208/how-do-i-count-how-many-text-entries-in-column-a-match-the-entry-in-column-b/?answer=31250#post-id-31250The following seems to work, using array functions. For the cell value, type:
=SUM(A1:A5=B1:B5)
and then hit `Ctrl-Shift-Enter` to submit it as an array function.
Strangely, adding a space before and after the `=` sign breaks it...Fri, 14 Mar 2014 09:43:36 +0100https://ask.libreoffice.org/en/question/31208/how-do-i-count-how-many-text-entries-in-column-a-match-the-entry-in-column-b/?answer=31250#post-id-31250Answer by Regina for <p>I've tried constructs like COUNTIF(A1:A5, EXACT(A1,B1)) but nothing seems to work...</p>
<p>Update 1: I've added an example:</p>
<pre><code>| A | B |
| X | X | --> 1
| X | Y |
| Y | Y | --> 2
| Y | Z |
| Z | Z | --> 3
</code></pre>
<p>Here, it should return 3</p>
https://ask.libreoffice.org/en/question/31208/how-do-i-count-how-many-text-entries-in-column-a-match-the-entry-in-column-b/?answer=31217#post-id-31217<code>=COUNTIF(A1:A100;B1)</code> For to only count exact matching, check the option "Search criteria = and <> must apply to whole cells" in Tools > Options > Calc > Calculate.
If you have continuous data and column B contains a classification with increasing boundaries, the function FREQUENCY might work as well. [ lower boundary < class <= upper boundary ]Thu, 13 Mar 2014 21:04:44 +0100https://ask.libreoffice.org/en/question/31208/how-do-i-count-how-many-text-entries-in-column-a-match-the-entry-in-column-b/?answer=31217#post-id-31217Comment by Michael Clerx for <p><code>=COUNTIF(A1:A100;B1)</code> For to only count exact matching, check the option "Search criteria = and <> must apply to whole cells" in Tools > Options > Calc > Calculate.</p>
<p>If you have continuous data and column B contains a classification with increasing boundaries, the function FREQUENCY might work as well. [ lower boundary < class <= upper boundary ]</p>
https://ask.libreoffice.org/en/question/31208/how-do-i-count-how-many-text-entries-in-column-a-match-the-entry-in-column-b/?comment=31248#post-id-31248I tried this (and again, using your exact syntax) and it simply doesn't work :(Fri, 14 Mar 2014 09:23:18 +0100https://ask.libreoffice.org/en/question/31208/how-do-i-count-how-many-text-entries-in-column-a-match-the-entry-in-column-b/?comment=31248#post-id-31248