Need to count number of entered chapters (Rows) for each Title

[SnakeDoctor]

Greetings!

We have numerous Manuals we need to review chapter-by-chapter and track the progress of the review of each Manual.

I am separating each Manual by entering only the titles in Column A, each title in its own row, and then entering each Chapter name in a separate row in Column B. Effectively separating each book title (Column A) using the book chapters (Column B).

What I need is to count the number rows entered between each Title, so I have a value to calculate the percentage of completed chapter reviews from.



Can anyone help me out with this?

I have attached a copy of the Workbook, I was trying calculations in Columns L & M to hide the totals when I hide the columns. And I will add the percentage complete in Column D of each Title Row.

[batman1]

I don't know English and I don't know if I understand correctly. Do you want L7 = 13?

If so, the formula for L7, change ";" to "," if necessary

=IF(B7="";"";COUNTA(C8:INDEX(C8:C$1000;MATCH("*";B8:B$1000;0)-1;0)))

[SnakeDoctor]

After several years looking for the answer to this question, I finally found a way to do this!

To create a percentage you need, of course, the values that define the percentage. In my example, data is separated into individual blocks - by manual title. And the number of chapters in each manual will change as the manual expands, leaving a problem of how to separate chapter rows of one title from the next - so the percentage of completed chapter reviews for each title is accurate.

The solution I found was to create 'Named Blocks' for each Manual Title, by highlighting the Title row and (whatever number) the next 10 blank rows, then going up to the 'Name Box' and giving that block a unique name.

I then locked the cells of each entire title row (I password every Workbook I create to prevent users from accidentally deleting/changing formulas, etc.). I went a few columns outside of the main page, labeled and entered the two percentage-base values:
1. Number of Chapters [rows under title] using: =ROWS(BLOCK_NAME)-1 (-1 to remove the Title row itself from the count.)
2. Number Completed [cells with chosen 'completed' indicator] using: =COUNTIFS(BLOCK_NAME, "Approved*)
Now the 'Percentage Complete' data is ready to source. I chose two cells in the Title row (in the main page area) to label and enter the percentage value:
3. Percent Completed using: =[Step2 Cell]/[Step1 Cell]*100
And voila!

I also used Conditional Formatting for several functions, check it all out in the new example I've added!

[batman1]

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)

=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

=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)

=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

[SnakeDoctor]

Hey, Batman1! Thanks for helping out with this, I really appreciate it

This example is strictly the template (and example) for this workbook. I created 10 blank rows under each Title row simply for data uniformity during testing and then blanked each row to create a sanitized example to post here.

The number of titles in Column B will (only) increase depending on the number of manuals created as the project moves forward.

The number, composition, and details of chapters for each manual created (Columns C~H) should (only) increase depending on the number of manual chapters created as the project moves forward.

e.g., There can be Manuals with anywhere from many to just a few Chapters.

Again, I had been looking for a solution to this for a while, and there are now several workbooks the company wanted created using the same 'titled blocks of data' method.

I will try this with your formulas now!

Thanks, again! I'll let you know how it goes!

[ArviLaanemets]

Maybe consider design in added example

Data entry table is designed to make data entry and reading info from there using formulas as easy as possible. For viewing the entered data, you can design as many different report sheets as you need, which display the data read from data entry sheet exactly in format you want. As example I added a possible report sheet too.

[batman1]

Sorry, but I don't understand what you want to do. I thought you wanted the formula in L7 in the first file. Now you've attached a completely different file. Besides, I don't understand what your problem is.

[Pecoflyer]

Please enter some desensitized data and manually mocked-op results in your workbook, so we can stop guessing at what you need. Thx

[Pecoflyer]

Have you done any progress on your request? Is this solved or do you need more help?