Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 14 Mar 2016 18:42:31 +0100Count how many times a set of criteria are met in an array of cellshttps://ask.libreoffice.org/en/question/66211/count-how-many-times-a-set-of-criteria-are-met-in-an-array-of-cells/ I'm trying to write a formula that will count up the number of instances a set of criteria are met in an array of cells
So far I have been using the SUMPRODUCT() function but it doesn't allow me to use multiple criteria for matching. For example,
> =SUMPRODUCT(A1:A100="MATCH1",B1:B100="MATCH2")
But what I really want is something like,
> =SUMPRODUCT(A1:A100="MATCH1",B1:B100="MATCH2a" || "MATCH2b")
Where the second criteria is cells that match either string "MATCH2a" OR "MATCH2b".
Any help would be appreciated.
Mike
Fri, 11 Mar 2016 22:38:24 +0100https://ask.libreoffice.org/en/question/66211/count-how-many-times-a-set-of-criteria-are-met-in-an-array-of-cells/Answer by m.a.riosv for <p>I'm trying to write a formula that will count up the number of instances a set of criteria are met in an array of cells</p>
<p>So far I have been using the SUMPRODUCT() function but it doesn't allow me to use multiple criteria for matching. For example,</p>
<blockquote>
<p>=SUMPRODUCT(A1:A100="MATCH1",B1:B100="MATCH2")</p>
</blockquote>
<p>But what I really want is something like, </p>
<blockquote>
<p>=SUMPRODUCT(A1:A100="MATCH1",B1:B100="MATCH2a" || "MATCH2b")</p>
</blockquote>
<p>Where the second criteria is cells that match either string "MATCH2a" OR "MATCH2b".</p>
<p>Any help would be appreciated.</p>
<p>Mike</p>
https://ask.libreoffice.org/en/question/66211/count-how-many-times-a-set-of-criteria-are-met-in-an-array-of-cells/?answer=66222#post-id-66222As the result of a comparision is 1 for True and 0 for False, it can be used in operations.
=SUMPRODUCT(A1:A100="MATCH1",((B1:B100="MATCH2a")+(B1:B100="MATCH2b"))>0)
or
=SUMPRODUCT(A1:A100="MATCH1",NOT(ISERROR(FIND(B1:B100," MATCH2a MATCH2b"))))
remember that FIND is case sensitive.
or with COUNTIFS() if it is in your version.Fri, 11 Mar 2016 23:33:32 +0100https://ask.libreoffice.org/en/question/66211/count-how-many-times-a-set-of-criteria-are-met-in-an-array-of-cells/?answer=66222#post-id-66222Comment by mikeg for <p>As the result of a comparision is 1 for True and 0 for False, it can be used in operations.</p>
<pre><code>=SUMPRODUCT(A1:A100="MATCH1",((B1:B100="MATCH2a")+(B1:B100="MATCH2b"))>0)
</code></pre>
<p>or</p>
<pre><code>=SUMPRODUCT(A1:A100="MATCH1",NOT(ISERROR(FIND(B1:B100," MATCH2a MATCH2b"))))
</code></pre>
<p>remember that FIND is case sensitive.</p>
<p>or with COUNTIFS() if it is in your version.</p>
https://ask.libreoffice.org/en/question/66211/count-how-many-times-a-set-of-criteria-are-met-in-an-array-of-cells/?comment=66475#post-id-66475Thanks, that not only helps with this problem, but provides me more general insight into using these functions.Mon, 14 Mar 2016 18:42:31 +0100https://ask.libreoffice.org/en/question/66211/count-how-many-times-a-set-of-criteria-are-met-in-an-array-of-cells/?comment=66475#post-id-66475