Formula to Count Column items until next blank cell

[2k05gt]

I am having trouble with A FORMULA
Sheet layout.

A Student.....B Task.............C Completed
1 Student#1..........................## Completed =COUNTA(INDIRECT(C2&:&ADDRESS(MATCH(TRUE,INDEX((A3:A200<>0),0),0),3),FALSE),"Y")
2..................Homework..........y
3..................Match.............. y
4..................Reading.............y
5..................Writing..............y
6..................Geography.........n
7..................Book Report.......n
8..................Science Project...y
9
10 Student#2..........................## Completed =COUNTA(INDIRECT(C11&:&ADDRESS(MATCH(TRUE,INDEX((C11:C200<>0),0),0),3),FALSE),"Y")
11................Homework..........y
12................ Match...............y
13................Reading.............y
14................Writing..............y
15................Geography.........n
16................Book Report.......n
17................Science Project...n
18................Fitness Award.....y
19................Baseball Team....y
20................Soccer Team......y
21
22 Student#3..........................## Completed =COUNTA(INDIRECT(C23&:&ADDRESS(MATCH(TRUE,INDEX((C22:C200<>0),0),0),3),FALSE),"Y")
23................Homework.........y
24................Match................y
25................Reading.............y
26................Writing..............y
27................Geography.........n
28................Science Project...y
29
30 Student#4..........................## Completed =COUNTA(INDIRECT(C31&:&ADDRESS(MATCH(TRUE,INDEX((C31:C200<>0),0),0),3),FALSE),"Y")

Formula in C1, C10, C22, C30 (Does not work but its close to what I need "I think")
I need to get the Cell Address for the blank rows 9, 21, 29 and so on
I think I need it stored in a cell for the next student row to use..

Since the rows will always fluxuate I can not rely on the last row being equal betewwn each student,
since their goals are different.

the Rows with the student name and goals are Grouped and I can click the "+" to colapse the students rows
so just the students name row is only visable

 

[a massive zebra]

I am having trouble with A FORMULA
Sheet layout.

A Student.....B Task.............C Completed
1 Student#1..........................## Completed =COUNTA(INDIRECT(C2&:&ADDRESS(MATCH(TRUE,INDEX((A3:A200<>0),0),0),3),FALSE),"Y")
2..................Homework..........y
3..................Match.............. y
4..................Reading.............y
5..................Writing..............y
6..................Geography.........n
7..................Book Report.......n
8..................Science Project...y
9
10 Student#2..........................## Completed =COUNTA(INDIRECT(C11&:&ADDRESS(MATCH(TRUE,INDEX((C11:C200<>0),0),0),3),FALSE),"Y")
11................Homework..........y
12................ Match...............y
13................Reading.............y
14................Writing..............y
15................Geography.........n
16................Book Report.......n
17................Science Project...n
18................Fitness Award.....y
19................Baseball Team....y
20................Soccer Team......y
21
22 Student#3..........................## Completed =COUNTA(INDIRECT(C23&:&ADDRESS(MATCH(TRUE,INDEX((C22:C200<>0),0),0),3),FALSE),"Y")
23................Homework.........y
24................Match................y
25................Reading.............y
26................Writing..............y
27................Geography.........n
28................Science Project...y
29
30 Student#4..........................## Completed =COUNTA(INDIRECT(C31&:&ADDRESS(MATCH(TRUE,INDEX((C31:C200<>0),0),0),3),FALSE),"Y")

Formula in C1, C10, C22, C30 (Does not work but its close to what I need "I think")
I need to get the Cell Address for the blank rows 9, 21, 29 and so on
I think I need it stored in a cell for the next student row to use..

Since the rows will always fluxuate I can not rely on the last row being equal betewwn each student,
since their goals are different.

the Rows with the student name and goals are Grouped and I can click the "+" to colapse the students rows
so just the students name row is only visable

Replace: =COUNTA(INDIRECT(C2&:&ADDRESS(MATCH(TRUE,INDEX((A3:A200<>0),0),0),3),FALSE),"Y")

with: =ADDRESS(MIN(IF(ISBLANK(C2:C200),ROW(C1:C200))),3)

Entered with CTRL+SHFT+ENTR, not just enter.

Replace: =COUNTA(INDIRECT(C11&:&ADDRESS(MATCH(TRUE,INDEX((C11:C200<>0),0),0),3),FALSE),"Y")

with: =ADDRESS(MIN(IF(ISBLANK(C11:C200),ROW(C11:C200))),3)

Entered with CTRL+SHFT+ENTR, not just enter.

Etc...

 

[2k05gt]

Thanks for the Reply,

It returns the Cells Address as $C$10 but how do I then count the range?

I still need to count how many "Y" are in the range.

Do I use this =COUNTA(INDIRECT(C2&:&{ADDRESS(MIN(IF(ISBLANK(C2:C200),ROW(C1:C200))),3)}),FALSE),"Y") ????

I need to some how concatenate the Starting Cell and the formula derived end cell .

So some how It looks like this to Excel =COUNTA(C2:C10, "Y")

.. I am confused..

 

[a massive zebra]

Try this:

=COUNTIF(INDIRECT(ADDRESS(ROW()+1,3)&":"&ADDRESS(MIN(IF(ISBLANK(C2:C200),ROW(C2:C200))),3)),"Y")

Entered with CTRL+SHFT+ENTR

 

[2k05gt]

Ok, that was it,
and I think I understand why it works and where my mistakes were, thanks

 

[a massive zebra]

Glad to help.