Countif with 2 or more data ranges in same column

[Doug]

Does Excel have a function that will allow me to count
only the data range for a specific title when there is
more than one data range in the same column? I am using
Excel 2002 on an XP OS.

Refering to the example below here is what I am trying to
accomplish:

Column "A" contains the titles and column "B" contains the
data. In this example the only 2 cells in column "A",
except for the heading row, that are not empty are A2,
which contains the title "ABC", and A8, which
contains "XYZ".

Which ever row the title in column "A" is in, the data for
that title begins in column "B" and ends just before the
empty cell as you go down column "B". Since ABC is in row
2 the data for ABC begins in cell B2. Since the first
empty cell in column B is B6 the end of the data range for
ABC is cell B5. The data range for XYZ starts at B8 and
ends at B15.

I am looking for a function that will allow me to count
the number of cells in column B that have data entry for
ABC separately and in addition to the data entry for XYZ.
The amount of data cells in column B will vary each day
for each title so I can't use a fixed range. On some days
there are more than 2 titles so it would be necessary to
count the data ranges for more than 2 titles. No matter
how many titles the data will always be in column "B" and
separated from the next title's data by empty cells.


A B
1 Title Data
2 ABC 123
3 246
4 123
5 369
6
7
8 XYZ 321
9 123
10 333
11 331
12 678
13 333
14 244
15 543

Thanks in advance,
Doug

 

[Aladin Akyurek]

C1: RowIdx, a label.

C2, copied down:

=IF(A2<>"",ROW(A2),"")

This formula creates an additional column of data.

E1 houses the lable Title.

E2 houses ABC, a title.

F1 houses the label Count.

F2:

=IF(ISNUMBER(G2),COUNT(INDEX(B:B,G2):INDEX(B:B,H2)),"")

Copy this down for additional titles in E.

G1 houses the label From.

G2, copied down:

=MATCH(E2,A:A,0)

H1 houses the label To.

H2, copied down:

=IF(LOOKUP(REPT("z",255),A:A)=E2,MATCH(9.99999999999999E+307,B:B),MIN(INDEX(
C:C,G2+1):INDEX(C:C,MATCH(9.99999999999999E+307,C:C)))-1)

Note that the formulas in G2 and H2 calculate the subranges for titles in E.