count non blank cells with criteria

[Guest]

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.

 

[Biff]

Hi!

Try this:

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

Biff

 

[Guest]

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]

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

 

[Guest]

Hi Biff,

The formula worked. Thanks a lot.

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

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Hi Biff,

The formula worked. Thanks a lot.