# 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!

edit retag close merge delete

Sort by » oldest newest most voted

Used SUMPRODUCT like this:

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

so no need for array formula!

more

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.

( 2020-01-15 22:23:13 +0200 )edit

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.

( 2020-01-16 10:40:11 +0200 )edit

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

( 2020-01-17 21:09:30 +0200 )edit

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.

( 2020-01-20 00:23:15 +0200 )edit

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

more

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

( 2020-01-20 00:26:04 +0200 )edit

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.