count non empty cells if other cell has certain value
 

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

count non empty cells if other cell has certain value
 

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" wrote in message
...
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

count non empty cells if other cell has certain value
 

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

"Andy" wrote:

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" wrote in message
...
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

count non empty cells if other cell has certain value
 

Hi

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

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

Andy.

"Nico" wrote in message
...
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

"Andy" wrote:

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" wrote in message
...
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

count non empty cells if other cell has certain value
 

Andy,

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

Grtz,
Nico

"Andy" wrote:

Hi

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

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

Andy.

"Nico" wrote in message
...
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

"Andy" wrote:

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" wrote in message
...
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

count non empty cells if other cell has certain value
 

Glad to help - and thanks for the feedback!

Andy.

"Nico" wrote in message
...
Andy,

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

Grtz,
Nico

"Andy" wrote:

Hi

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

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

Andy.

"Nico" wrote in message
...
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

"Andy" wrote:

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" wrote in message
...
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