Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 06 Jul 2012 08:32:52 +0200SUMIF with Functions?https://ask.libreoffice.org/en/question/3692/sumif-with-functions/Hi, I'm not sure this is even possible, but I'm hoping someone can show me how if it is (and that it is). The best way I can think to describe this is to make a little diagram, so I hope this is readable.
A B C
1 5 1 A
2 7 3 B
3 2 2 A
4 9 4 C
Ok, what I want to do is make 1 cell (let's say D5, just for kicks) give me the sum of column A where column C is "A" (so 7, 5 from row 1 and 2 from row 3) and the max of column B where column C is "A" (so 2, for row 3, since row 3's 2 is higher than row 1's 1), for, in this case, a total of 9.
I tried =SUMIF(C1:C4,"A",A1:A4+MAX(B1:B4)), but that didn't work. Nor did =SUMIF(C1:C4,"A",A1:A4)+SUMIF(C1:C4,"A",MAX(B1:B4)). Both of those give Error code 504 (SUMIF doesn't like any other functions apparently). I also tried =SUMIF(C1:C4,"A",A1:A4)+IF(C1:C4,"A",MAX(B1:B4)), which doesn't give an error but gives the max of column B regardless of whether its corresponding cell in column C is "A" or not.
Is there any way to do this using only the single cell for the total (as opposed to making separate formulae for the sum of A and the max of B and then referencing those with a third formula)?Mon, 02 Jul 2012 07:02:25 +0200https://ask.libreoffice.org/en/question/3692/sumif-with-functions/Answer by JohnSUN for <p>Hi, I'm not sure this is even possible, but I'm hoping someone can show me how if it is (and that it is). The best way I can think to describe this is to make a little diagram, so I hope this is readable.</p>
<pre><code> A B C
1 5 1 A
2 7 3 B
3 2 2 A
4 9 4 C
</code></pre>
<p>Ok, what I want to do is make 1 cell (let's say D5, just for kicks) give me the sum of column A where column C is "A" (so 7, 5 from row 1 and 2 from row 3) and the max of column B where column C is "A" (so 2, for row 3, since row 3's 2 is higher than row 1's 1), for, in this case, a total of 9.</p>
<p>I tried =SUMIF(C1:C4,"A",A1:A4+MAX(B1:B4)), but that didn't work. Nor did =SUMIF(C1:C4,"A",A1:A4)+SUMIF(C1:C4,"A",MAX(B1:B4)). Both of those give Error code 504 (SUMIF doesn't like any other functions apparently). I also tried =SUMIF(C1:C4,"A",A1:A4)+IF(C1:C4,"A",MAX(B1:B4)), which doesn't give an error but gives the max of column B regardless of whether its corresponding cell in column C is "A" or not. </p>
<p>Is there any way to do this using only the single cell for the total (as opposed to making separate formulae for the sum of A and the max of B and then referencing those with a third formula)?</p>
https://ask.libreoffice.org/en/question/3692/sumif-with-functions/?answer=3723#post-id-3723Try the "array formula":
{=SUMIF(C1:C4;"A";A1:A4)+MAX((B1:B4)*(C1:C4="A"))}
(End the entry the formula by pressing the Ctrl+Shift+Enter)Fri, 06 Jul 2012 08:32:52 +0200https://ask.libreoffice.org/en/question/3692/sumif-with-functions/?answer=3723#post-id-3723