Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 23 Jul 2014 23:09:04 +0200How to Sum only if range totals greater than 3https://ask.libreoffice.org/en/question/37366/how-to-sum-only-if-range-totals-greater-than-3/hi, Im trying to create a field totalling how many "triple play" orders I've gotten throughout the day. So far Ive done some pretty decent formulas to keep track of my sales, but this particular one eludes me.
If the Cable, Tv, and Phone cells each have a 1 in them, then I'd like the Triple Play Totals cell to totall how many rows (accounts) have all three of those cells marked. If a row (account) doesnt have all three marked with 1's then the Triple Play Totals field doesnt add them in the total.
I hope this makes sense, any advice is appreciately very much.
Tue, 22 Jul 2014 20:57:33 +0200https://ask.libreoffice.org/en/question/37366/how-to-sum-only-if-range-totals-greater-than-3/Answer by m.a.riosv for <p>hi, Im trying to create a field totalling how many "triple play" orders I've gotten throughout the day. So far Ive done some pretty decent formulas to keep track of my sales, but this particular one eludes me.</p>
<p>If the Cable, Tv, and Phone cells each have a 1 in them, then I'd like the Triple Play Totals cell to totall how many rows (accounts) have all three of those cells marked. If a row (account) doesnt have all three marked with 1's then the Triple Play Totals field doesnt add them in the total.</p>
<p>I hope this makes sense, any advice is appreciately very much.</p>
https://ask.libreoffice.org/en/question/37366/how-to-sum-only-if-range-totals-greater-than-3/?answer=37371#post-id-37371If I have understood your question, next a couple of formulas to do it:
=SUMPRODUCT($A$2:$A$6;$B$2:$B$6;$C$2:$C$6)
=COUNTIFS($A$2:$A$6;1;$B$2:$B$6;1;$C$2:$C$6;1)
attached a sample file, [CountWithSeveralConditions.ods](/upfiles/14060639315521122.ods)
There are more options but I think they implies to be introduced as arrays what sometimes is not so easy, while SUMPRODUCT() even working as array doesn't need to be enter as such.Tue, 22 Jul 2014 23:27:36 +0200https://ask.libreoffice.org/en/question/37366/how-to-sum-only-if-range-totals-greater-than-3/?answer=37371#post-id-37371Comment by netshark for <p>If I have understood your question, next a couple of formulas to do it:</p>
<p>=SUMPRODUCT($A$2:$A$6;$B$2:$B$6;$C$2:$C$6)</p>
<p>=COUNTIFS($A$2:$A$6;1;$B$2:$B$6;1;$C$2:$C$6;1)</p>
<p>attached a sample file, <a href="/upfiles/14060639315521122.ods">CountWithSeveralConditions.ods</a></p>
<p>There are more options but I think they implies to be introduced as arrays what sometimes is not so easy, while SUMPRODUCT() even working as array doesn't need to be enter as such.</p>
https://ask.libreoffice.org/en/question/37366/how-to-sum-only-if-range-totals-greater-than-3/?comment=37413#post-id-37413Thank you for replying, I tried using your example above but not completely understanding it. (Tried to open the file you sent but it isnt opening correctly, and not sure which file there are many xml files inside it, no osd files)
But to clarify what I would like to do is make it so if 3 cells in a row have a number one in them, then count that row as 1.
for example =If K16:N16 is equal to or greater than 3 total, then count this row as value of one. And each row will have same formula.Wed, 23 Jul 2014 23:09:04 +0200https://ask.libreoffice.org/en/question/37366/how-to-sum-only-if-range-totals-greater-than-3/?comment=37413#post-id-37413