software

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

For example i want to count number of entries in Column quot;Bquot; for quot;ANTH 328quot;
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=quot;ANTH 328quot;),--(B1:B20lt;gt;quot;quot;))

Biff

quot;UTquot; gt; wrote in message
...
gt;I need to count non blank cells that match a condition.
gt;
gt; For example i want to count number of entries in Column quot;Bquot; for quot;ANTH
gt; 328quot;
gt; course.
gt;
gt; A B
gt; ANTH 328 PC
gt; ANTH 328
gt; ANTH 328 PC
gt;
gt; I have tried DCOUNTA and combination of functions. Nothing is working.
gt; Help
gt; 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 quot;ANTH 328quot;. Any other
guesses?

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; Try this:
gt;
gt; =SUMPRODUCT(--(A1:A20=quot;ANTH 328quot;),--(B1:B20lt;gt;quot;quot;))
gt;
gt; Biff
gt;
gt; quot;UTquot; gt; wrote in message
gt; ...
gt; gt;I need to count non blank cells that match a condition.
gt; gt;
gt; gt; For example i want to count number of entries in Column quot;Bquot; for quot;ANTH
gt; gt; 328quot;
gt; gt; course.
gt; gt;
gt; gt; A B
gt; gt; ANTH 328 PC
gt; gt; ANTH 328
gt; gt; ANTH 328 PC
gt; gt;
gt; gt; I have tried DCOUNTA and combination of functions. Nothing is working.
gt; gt; Help
gt; gt; will be greatly appreciated.
gt;
gt;
gt;

Hi!

Take a look at this screencap:

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

If you're getting a result of 3 based on your example then the quot;blankquot; 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

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

The formula worked. Thanks a lot.

quot;Biffquot; wrote:

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

You're welcome. Thanks for the feedback!

Biff

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