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!

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!

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

Used SUMPRODUCT like this:

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

so no need for array formula!

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.

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

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.

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

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