Formula to Count Column items until next blank cell

2k05gt

Board Regular
Joined
Sep 1, 2006
Messages
151
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
 
Last edited:

a massive zebra

Board Regular
Joined
Dec 1, 2009
Messages
87
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...
 
Last edited:

2k05gt

Board Regular
Joined
Sep 1, 2006
Messages
151
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.. :(
 
Last edited:

a massive zebra

Board Regular
Joined
Dec 1, 2009
Messages
87
Try this:

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

Entered with CTRL+SHFT+ENTR
 
Last edited:

2k05gt

Board Regular
Joined
Sep 1, 2006
Messages
151
Ok, that was it,
and I think I understand why it works and where my mistakes were, thanks
 

Forum statistics

Threads
1,086,259
Messages
5,388,728
Members
402,137
Latest member
pkulkarni

Some videos you may like

This Week's Hot Topics

Top