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:  nico
Date: Thu, 18 May 2006

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" <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

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