count non empty cells if other cell has certain value

[Nico]

Hello,
I'm trying to do the following:
col-a col-b
1 d
2 x
2 s
2
3 d

In a cell I want to count the number of non-empty cells in col-b where the
value of col-a is 2. The answer for this example should be 2 because there
are 3 rows with value 2 in col-a but only 2 with a value in col-b.

I tried all sorts of ways (like counta, sumif etc.) but somehow I'm not
succeeding.
Can anyone give me a hint ?

Thanks in advance,
Nico

 

[Guest]

Hi

Try this:
=SUMPRODUCT((A2:A6=2)*(B2:B6<>""))
This function cannot use full columns as references and the ranges must be
the same size.

Hope this helps.
Andy.

 

[Nico]

Andy,

My example was not complete, because in the first column there can also be
letters. If this is the case then the formule doesn't work. If I try it with
numbers, it works fine.

Any way to work around this ?
Thanks for your response.
Nico

 

[Guest]

Hi

With the letters, instead of 2 use "a" - or whatever:

=SUMPRODUCT((A2:A6="a")*(B2:B6<>""))

Andy.

 

[Nico]

Andy,

Thanks very much, it works just like I wanted.
And if you see it, it always looks so easy... ;-)

Grtz,
Nico

 

[Guest]

Glad to help - and thanks for the feedback!

Andy.