Ask Your Question
0

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

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

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.

help!

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

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

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
0

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

Lizat gravatar image

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

Comments

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.

Opaque gravatar imageOpaque ( 2020-01-15 22:23:13 +0100 )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 +0100 )edit
0

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

Opaque gravatar image

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

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 18 times

Last updated: yesterday