EXCEL-- Counting Occurences in a range based on criteria 1

[johnywest]

Hi all,
Here is what I am doing. I have 2 colums one has a code other is a name field. I want to count how many occurences of a code based on name field having data in it.

Example: A B
CO FRED
CO TOM
CO
XR TIM
TT JIM
I need a total count of occurences of "CO" where there is a name in colum "B" this answer would be "2" the blank is not counted

Here is my problem using the following formula I do get the result but as data changes and let's say I delete a name from colum "B" my count does not change until I use the "clear contets" of cell before it gives proper result. here is the formula

=SUM(IF(I9:I114="co",IF(J9:J114="",0,1)))

Is there a way to count these occurences while the data changes without having to use the clear contents function vice delete in the cell?

any help is greatly appreciated.
John

 

[xlbo]

Try this:
=SUM((A1:A100="CO&quot*(NOT(ISBLANK(B1:B100))))
enter using CTRL+SHIFT+ENTER (array formula - you will see { } round the formula if done correctly)

assuming data in A1:B100 Rgds
Geoff
Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so. Douglas Adams

 

[johnywest]

Geoff,
Thanks that worked great! These formulas drive me crazy like big puzzles....

John