Re: count non empty cells if other cell has certain value

Giganews Newsgroups
Subject: Re: count non empty cells if other cell has certain value
Posted by:  Andy
Date: Thu, 18 May 2006

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" <Ni…@discussions.microsoft.com> wrote in message
news:0016F98D-D551-4C97-8A3C-4C2CA4A7B1…@microsoft.com...
> 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

Replies

In response to

count non empty cells if other cell has certain value posted by nico on Thu, 18 May 2006