Count all values in column 2 based on values in column 1

Lizat · January 15, 2020, 6:28pm

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!

anon73440385 · January 15, 2020, 6:59pm

Hello,

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.

Lizat · January 15, 2020, 7:11pm

Used SUMPRODUCT like this:

=SUMPRODUCT((O1:O1000=“y”) * (P1:P1000=“y”) * (S1:S1000>0) * (S1:S1000<>“y”))

so no need for array formula!

anon73440385 · January 15, 2020, 9:23pm

Just 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.

Lizat · January 16, 2020, 9:40am

Thank 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.

Lizat · January 17, 2020, 8:09pm

correct the presentaion by adding spaces around the asterisks (odd1)

erAck · January 19, 2020, 11:23pm

I 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.

schlabs · January 15, 2020, 7:28pm

The 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 mistake

erAck · January 19, 2020, 11:26pm

It’s also the slowest calculating way in this context.