Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 20 Jan 2020 00:26:04 +0100Count all values in column 2 based on values in column 1https://ask.libreoffice.org/en/question/225177/count-all-values-in-column-2-based-on-values-in-column-1/ I want to do this:
For every 'y' in column1 count the corresponding cell in column 2 (same row) if it is not 'y' and is not blank.
I think I need to use an array formula but i have forgotten how to do this.
help!Wed, 15 Jan 2020 19:28:18 +0100https://ask.libreoffice.org/en/question/225177/count-all-values-in-column-2-based-on-values-in-column-1/Answer by Lizat for <p>I want to do this: </p>
<p>For every 'y' in column1 count the corresponding cell in column 2 (same row) if it is not 'y' and is not blank.</p>
<p>I think I need to use an array formula but i have forgotten how to do this.</p>
<p>help!</p>
https://ask.libreoffice.org/en/question/225177/count-all-values-in-column-2-based-on-values-in-column-1/?answer=225184#post-id-225184Used SUMPRODUCT like this:
=SUMPRODUCT((O1:O1000="y") * (P1:P1000="y") * (S1:S1000>0) * (S1:S1000<>"y"))
so no need for array formula!Wed, 15 Jan 2020 20:11:59 +0100https://ask.libreoffice.org/en/question/225177/count-all-values-in-column-2-based-on-values-in-column-1/?answer=225184#post-id-225184Comment by erAck for <p>Used SUMPRODUCT like this:</p>
<p>=SUMPRODUCT((O1:O1000="y") * (P1:P1000="y") * (S1:S1000>0) * (S1:S1000<>"y"))</p>
<p>so no need for array formula!</p>
https://ask.libreoffice.org/en/question/225177/count-all-values-in-column-2-based-on-values-in-column-1/?comment=225790#post-id-225790I don't know where people get the idea of using an extra multiplication within one SUMPRODUCT() parameter, the function **sums** the **product** of its arguments, hence the name.. just placing the conditions as separate parameters' arguments is the straight forward way to write it:
=SUMPRODUCT(O1:O1000="y"; P1:P1000="y"; S1:S1000>0; S1:S1000<>"y")
With the additional advantage that then a faster algorithm is used for typed matrix segments multiplication.Mon, 20 Jan 2020 00:23:15 +0100https://ask.libreoffice.org/en/question/225177/count-all-values-in-column-2-based-on-values-in-column-1/?comment=225790#post-id-225790Comment by Lizat for <p>Used SUMPRODUCT like this:</p>
<p>=SUMPRODUCT((O1:O1000="y") * (P1:P1000="y") * (S1:S1000>0) * (S1:S1000<>"y"))</p>
<p>so no need for array formula!</p>
https://ask.libreoffice.org/en/question/225177/count-all-values-in-column-2-based-on-values-in-column-1/?comment=225545#post-id-225545correct the presentaion by adding spaces around the asterisks (odd1)Fri, 17 Jan 2020 21:09:30 +0100https://ask.libreoffice.org/en/question/225177/count-all-values-in-column-2-based-on-values-in-column-1/?comment=225545#post-id-225545Comment by Lizat for <p>Used SUMPRODUCT like this:</p>
<p>=SUMPRODUCT((O1:O1000="y") * (P1:P1000="y") * (S1:S1000>0) * (S1:S1000<>"y"))</p>
<p>so no need for array formula!</p>
https://ask.libreoffice.org/en/question/225177/count-all-values-in-column-2-based-on-values-in-column-1/?comment=225273#post-id-225273Thank you
In fact I copied the formula, but somehow the asterisks are not shown except for the last one - I didn't notice in preview but have checked by going into edit and they are there. I didn't use the code style so I suppose it's a problem with typing formulae using normal style.Thu, 16 Jan 2020 10:40:11 +0100https://ask.libreoffice.org/en/question/225177/count-all-values-in-column-2-based-on-values-in-column-1/?comment=225273#post-id-225273Comment by Opaque for <p>Used SUMPRODUCT like this:</p>
<p>=SUMPRODUCT((O1:O1000="y") * (P1:P1000="y") * (S1:S1000>0) * (S1:S1000<>"y"))</p>
<p>so no need for array formula!</p>
https://ask.libreoffice.org/en/question/225177/count-all-values-in-column-2-based-on-values-in-column-1/?comment=225196#post-id-225196Just to mention that your formula gives an `Err:508` (Error: bracketing)
`=SUMPRODUCT((O1:O1000="y")*(P1:P1000="y")*(S1:S1000>0)*(S1:S1000<>"y"))`
may be the correct one.Wed, 15 Jan 2020 22:23:13 +0100https://ask.libreoffice.org/en/question/225177/count-all-values-in-column-2-based-on-values-in-column-1/?comment=225196#post-id-225196Answer by schlabs for <p>I want to do this: </p>
<p>For every 'y' in column1 count the corresponding cell in column 2 (same row) if it is not 'y' and is not blank.</p>
<p>I think I need to use an array formula but i have forgotten how to do this.</p>
<p>help!</p>
https://ask.libreoffice.org/en/question/225177/count-all-values-in-column-2-based-on-values-in-column-1/?answer=225185#post-id-225185The most clear way to do and understand the work is create one column for internal calc ( can be done in other sheet).
For example take the C1 and put:
=IF(A1='y' ; B1 ; 0)
You will get the B1 value when A1 is equal to Y, or Zero when is different.
Finallly you can sum C column
Is not the only way to do, but i think that is very easy found any mistakeWed, 15 Jan 2020 20:28:54 +0100https://ask.libreoffice.org/en/question/225177/count-all-values-in-column-2-based-on-values-in-column-1/?answer=225185#post-id-225185Comment by erAck for <p>The most clear way to do and understand the work is create one column for internal calc ( can be done in other sheet). </p>
<p>For example take the C1 and put:
=IF(A1='y' ; B1 ; 0)
You will get the B1 value when A1 is equal to Y, or Zero when is different.
Finallly you can sum C column</p>
<p>Is not the only way to do, but i think that is very easy found any mistake</p>
https://ask.libreoffice.org/en/question/225177/count-all-values-in-column-2-based-on-values-in-column-1/?comment=225791#post-id-225791It's also the slowest calculating way in this context.Mon, 20 Jan 2020 00:26:04 +0100https://ask.libreoffice.org/en/question/225177/count-all-values-in-column-2-based-on-values-in-column-1/?comment=225791#post-id-225791Answer by Opaque for <p>I want to do this: </p>
<p>For every 'y' in column1 count the corresponding cell in column 2 (same row) if it is not 'y' and is not blank.</p>
<p>I think I need to use an array formula but i have forgotten how to do this.</p>
<p>help!</p>
https://ask.libreoffice.org/en/question/225177/count-all-values-in-column-2-based-on-values-in-column-1/?answer=225180#post-id-225180Hello,
not sure, whether I got you right. The following formula counts, with following conditions
- cell in column `A` has value `y` **--and--**
- cell in colum `B` is not empty **--and--**
- cell in colum `B` is not `y`
`=SUMPRODUCT((A:A="y");(B:B<>"");(B:B<>"y"))`
**Tested using LibreOffice:**
Version: 6.3.4.2,Build ID: 60da17e045e08f1793c57c00ba83cdfce946d0aa
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kde5;
Locale: en-US (en_US.UTF-8); UI-Language: en-US,Calc: threaded
Hope that helps.
*If the answer helped to solve your problem, please click the check mark (✔) next to the answer.*
Wed, 15 Jan 2020 19:59:01 +0100https://ask.libreoffice.org/en/question/225177/count-all-values-in-column-2-based-on-values-in-column-1/?answer=225180#post-id-225180