Ask Your Question

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

asked 2020-01-15 19:28:18 +0200

Lizat gravatar image

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.


edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2020-01-15 20:11:59 +0200

Lizat gravatar image

updated 2020-01-17 21:08:46 +0200

Used SUMPRODUCT like this:

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

so no need for array formula!

edit flag offensive delete link more


Just to mention that your formula gives an Err:508 (Error: bracketing)


may be the correct one.

Opaque gravatar imageOpaque ( 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.

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

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

Lizat gravatar imageLizat ( 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.

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

answered 2020-01-15 20:28:54 +0200

schlabs gravatar image

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

edit flag offensive delete link more


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

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

answered 2020-01-15 19:59:01 +0200

Opaque gravatar image

updated 2020-01-15 20:00:22 +0200


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


Tested using LibreOffice:

Version:,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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-01-15 19:28:18 +0200

Seen: 54 times

Last updated: Jan 17