# 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

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

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.

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.

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.