Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 03 Apr 2020 07:42:08 +0200Dynamically sum the value of sets of cells separated by an empty row?https://ask.libreoffice.org/en/question/235204/dynamically-sum-the-value-of-sets-of-cells-separated-by-an-empty-row/Not sure how to word this, but is there a good way to dynamically sum the value of sets of cells separated by an empty row? Or anything to that effect. E.g.:
| Person A | approved | 1/25 |
| Person A | approved | 1/26 |
| | | |
| Person B | denied | 1/29 |
| Person B | denied | 2/02 |
| Person B | approved | 2/12 |
| | | |
| Person C | denied | 1/25 |
| | | |
I would like a cell to sum up the number of approvals for each person. However,
I will continually add rows for each person. I do not want to move Person B or
C to a different spreadsheet (unless there is no good solution) or shift them
to different columns. The problem is I cannot specify a range of cells because I will keep adding entries over time. I cannot specify the entire column either as I want the data of Person A, B, and C to be structured in this way on the same spreadsheet in order to easily compare the data of each person.
Wed, 25 Mar 2020 22:00:00 +0100https://ask.libreoffice.org/en/question/235204/dynamically-sum-the-value-of-sets-of-cells-separated-by-an-empty-row/Answer by SM_Riga for <p>Not sure how to word this, but is there a good way to dynamically sum the value of sets of cells separated by an empty row? Or anything to that effect. E.g.:</p>
<pre><code>| Person A | approved | 1/25 |
| Person A | approved | 1/26 |
| | | |
| Person B | denied | 1/29 |
| Person B | denied | 2/02 |
| Person B | approved | 2/12 |
| | | |
| Person C | denied | 1/25 |
| | | |
</code></pre>
<p>I would like a cell to sum up the number of approvals for each person. However,
I will continually add rows for each person. I do not want to move Person B or
C to a different spreadsheet (unless there is no good solution) or shift them
to different columns. The problem is I cannot specify a range of cells because I will keep adding entries over time. I cannot specify the entire column either as I want the data of Person A, B, and C to be structured in this way on the same spreadsheet in order to easily compare the data of each person.</p>
https://ask.libreoffice.org/en/question/235204/dynamically-sum-the-value-of-sets-of-cells-separated-by-an-empty-row/?answer=235211#post-id-235211Hello @zf
Assuming `A column` is for Person and `B column` for status:
`=SUMPRODUCT(A:A="Person A";B:B="approved")` - counts Person A approvals
`=SUMPRODUCT(A:A="Person B";B:B="approved")` - counts Person B approvals
And so on. Or:
`=COUNTIFS(A:A;"Person A";B:B;"approved")` - counts Person A approvals
`=COUNTIFS(A:A;"Person B";B:B;"approved")` - counts Person B approvalsWed, 25 Mar 2020 22:28:51 +0100https://ask.libreoffice.org/en/question/235204/dynamically-sum-the-value-of-sets-of-cells-separated-by-an-empty-row/?answer=235211#post-id-235211Comment by zf for <p>Hello <a href="/en/users/78488/zf/">@zf</a></p>
<p>Assuming <code>A column</code> is for Person and <code>B column</code> for status:</p>
<p><code>=SUMPRODUCT(A:A="Person A";B:B="approved")</code> - counts Person A approvals</p>
<p><code>=SUMPRODUCT(A:A="Person B";B:B="approved")</code> - counts Person B approvals</p>
<p>And so on. Or:</p>
<p><code>=COUNTIFS(A:A;"Person A";B:B;"approved")</code> - counts Person A approvals</p>
<p><code>=COUNTIFS(A:A;"Person B";B:B;"approved")</code> - counts Person B approvals</p>
https://ask.libreoffice.org/en/question/235204/dynamically-sum-the-value-of-sets-of-cells-separated-by-an-empty-row/?comment=236689#post-id-236689@SM_Riga Thanks. If I wanted to test that one of the criteria is a number (or better yet, a date, but I'm not sure how that's possible), how would I do it with `COUNTIFS` since it is way more performant for me than `SUMPRODUCT`? E.g. the `COUNTIFS` equivalent of `SUMPRODUCT(A:A="Person 1",G:G="P",ISNUMBER(P:P),P:P>EDATE(TODAY(),-12))`?Fri, 03 Apr 2020 07:42:08 +0200https://ask.libreoffice.org/en/question/235204/dynamically-sum-the-value-of-sets-of-cells-separated-by-an-empty-row/?comment=236689#post-id-236689