Quote:
Originally Posted by SnakeDoctor
After several years looking for the answer to this question, I finally found a way to do this!!
|
I don't understand.
You wrote: "What I need is to count the number of rows entered between each Title, so I have a value to calculate the percentage of completed chapter reviews from."
And in the file you entered the data so that each title has exactly 10 rows.
Does each title always have 10 towers? If so, why manually create 10 NAMEs and manually enter formulas 10 times in M7, M18, M29, ...?
You simply always have 10 rows for each title.
Besides, if you have 50 titles, do you also manually create NAMEs 50 times and enter formulas 50 times in columns M and O (50*2 = 100 times)?
If the number of rows between titles in column B changes for each title, then:
1. If you want to count the number of empty and non-empty rows between titles, then:
Formula in M7 (file in #6)
Code:
=IF(B7="";"";MATCH("*";B8:B$1005;0)-1)
and copy down to the last title line
2. If you want to count the number of non-empty lines between titles, the formula is given in post #2.
In this case, the formula for M7
Code:
=IF(B7="";"";COUNTA(C8:INDEX(C8:C$1000;MATCH("*";B8:B$1000;0)-1;0)))
I think you want point 2.
----------------
Formula for O7 (note: "Approved" is in column E - file in #6)
Code:
=IF(B7="";"";COUNTIFS(E8:INDEX(E8:E$1000;MATCH("*";B8:B$1000;0)-1;0); "Approved*"))
-------------------
Note:
1. If the formula causes an error, try changing ";" to ","
2. There must be at least 1 non-empty cell after the last title in column B. In the file with #6, cell B130 <> "". If there are only empty cells from B122 down, you need to enter B122 = "hic hic hic" or whatever