count non blank cells with criteria

I need to count non blank cells that match a condition.

For example i want to count number of entries in Column "B" for "ANTH 328"
course.

A B
ANTH 328 PC
ANTH 328
ANTH 328 PC

I have tried DCOUNTA and combination of functions. Nothing is working. Help
will be greatly appreciated.

Hi!

Try this:

=SUMPRODUCT(--(A1:A20="ANTH 328"),--(B1:B20<>""))

Biff

"UT" <[email protected]> wrote in message
news:[email protected]...
>I need to count non blank cells that match a condition.
>
> For example i want to count number of entries in Column "B" for "ANTH
> 328"
> course.
>
> A B
> ANTH 328 PC
> ANTH 328
> ANTH 328 PC
>
> I have tried DCOUNTA and combination of functions. Nothing is working.
> Help
> will be greatly appreciated.

Hi Biff,

The function dosent work. It counts the number of entries in Column A . So
it gives me count of 3 in the example below. But it should actually show the
count of 2 since there are only two entries that match "ANTH 328". Any other
guesses?

"Biff" wrote:

> Hi!
>
> Try this:
>
> =SUMPRODUCT(--(A1:A20="ANTH 328"),--(B1:B20<>""))
>
> Biff
>
> "UT" <[email protected]> wrote in message
> news:[email protected]...
> >I need to count non blank cells that match a condition.
> >
> > For example i want to count number of entries in Column "B" for "ANTH
> > 328"
> > course.
> >
> > A B
> > ANTH 328 PC
> > ANTH 328
> > ANTH 328 PC
> >
> > I have tried DCOUNTA and combination of functions. Nothing is working.
> > Help
> > will be greatly appreciated.
>
>
>

Hi!

Take a look at this screencap:

http://img526.imageshack.us/img526/1241/sump0mi.jpg

If you're getting a result of 3 based on your example then the "blank" cell
is not blank. Is there a formula in the second column? There may be unseen
characters like spaces.

As you can see in the screencap the formula does return the correct result.

Biff

"UT" <[email protected]> wrote in message
news:[email protected]...
> Hi Biff,
>
> The function dosent work. It counts the number of entries in Column A . So
> it gives me count of 3 in the example below. But it should actually show
> the
> count of 2 since there are only two entries that match "ANTH 328". Any
> other
> guesses?
>
> "Biff" wrote:
>
>> Hi!
>>
>> Try this:
>>
>> =SUMPRODUCT(--(A1:A20="ANTH 328"),--(B1:B20<>""))
>>
>> Biff
>>
>> "UT" <[email protected]> wrote in message
>> news:[email protected]...
>> >I need to count non blank cells that match a condition.
>> >
>> > For example i want to count number of entries in Column "B" for "ANTH
>> > 328"
>> > course.
>> >
>> > A B
>> > ANTH 328 PC
>> > ANTH 328
>> > ANTH 328 PC
>> >
>> > I have tried DCOUNTA and combination of functions. Nothing is working.
>> > Help
>> > will be greatly appreciated.
>>
>>
>>

Hi Biff,

The formula worked. Thanks a lot.

"Biff" wrote:

> Hi!
>
> Take a look at this screencap:
>
> http://img526.imageshack.us/img526/1241/sump0mi.jpg
>
> If you're getting a result of 3 based on your example then the "blank" cell
> is not blank. Is there a formula in the second column? There may be unseen
> characters like spaces.
>
> As you can see in the screencap the formula does return the correct result.
>
> Biff
>
> "UT" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Biff,
> >
> > The function dosent work. It counts the number of entries in Column A . So
> > it gives me count of 3 in the example below. But it should actually show
> > the
> > count of 2 since there are only two entries that match "ANTH 328". Any
> > other
> > guesses?
> >
> > "Biff" wrote:
> >
> >> Hi!
> >>
> >> Try this:
> >>
> >> =SUMPRODUCT(--(A1:A20="ANTH 328"),--(B1:B20<>""))
> >>
> >> Biff
> >>
> >> "UT" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I need to count non blank cells that match a condition.
> >> >
> >> > For example i want to count number of entries in Column "B" for "ANTH
> >> > 328"
> >> > course.
> >> >
> >> > A B
> >> > ANTH 328 PC
> >> > ANTH 328
> >> > ANTH 328 PC
> >> >
> >> > I have tried DCOUNTA and combination of functions. Nothing is working.
> >> > Help
> >> > will be greatly appreciated.
> >>
> >>
> >>
>
>
>

You're welcome. Thanks for the feedback!

Biff

"UT" <[email protected]> wrote in message
news:[email protected]...
> Hi Biff,
>
> The formula worked. Thanks a lot.
>
> "Biff" wrote:
>
>> Hi!
>>
>> Take a look at this screencap:
>>
>> http://img526.imageshack.us/img526/1241/sump0mi.jpg
>>
>> If you're getting a result of 3 based on your example then the "blank"
>> cell
>> is not blank. Is there a formula in the second column? There may be
>> unseen
>> characters like spaces.
>>
>> As you can see in the screencap the formula does return the correct
>> result.
>>
>> Biff
>>
>> "UT" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi Biff,
>> >
>> > The function dosent work. It counts the number of entries in Column A .
>> > So
>> > it gives me count of 3 in the example below. But it should actually
>> > show
>> > the
>> > count of 2 since there are only two entries that match "ANTH 328". Any
>> > other
>> > guesses?
>> >
>> > "Biff" wrote:
>> >
>> >> Hi!
>> >>
>> >> Try this:
>> >>
>> >> =SUMPRODUCT(--(A1:A20="ANTH 328"),--(B1:B20<>""))
>> >>
>> >> Biff
>> >>
>> >> "UT" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >I need to count non blank cells that match a condition.
>> >> >
>> >> > For example i want to count number of entries in Column "B" for
>> >> > "ANTH
>> >> > 328"
>> >> > course.
>> >> >
>> >> > A B
>> >> > ANTH 328 PC
>> >> > ANTH 328
>> >> > ANTH 328 PC
>> >> >
>> >> > I have tried DCOUNTA and combination of functions. Nothing is
>> >> > working.
>> >> > Help
>> >> > will be greatly appreciated.
>> >>
>> >>
>> >>
>>
>>
>>