How many pages per character? (not a typo)

NOTE: if the tables below look smooshed, try clicking on the title above ("How many pages...")

---

Suppose you're writing a novel, or some other large-ish prose, and you want to balance the number of pages that your main characters or points-of-view (etc.) get. Or you just want to see how many pages each character (etc.) gets. If your magnum opus were a half-dozen pages or so, you could print it out and mark it up with colored highlighters or something, but if it's several hundreds of pages, with several dozen chapters, you might want to make a table.

action	#pages
Adam adapts	2
Emily eats	7
Harry harrumphs	1
Kayla kicks	8
Emily investigates	2
Harry gets a haircut	8

That's all well and good, but if the table has a hundred (or hundreds) of rows, how do you figure out the distribution of pages among these characters? You could split the #pages column into multiple columns—one for each of your characters, like this:

action	Adam	Emily	Harry	Kayla
Adam adapts	2
Emily eats		7
Harry harrumphs			1
Kayla kicks				8
Emily investigates		2
Harry gets a haircut			8

Then you can tell LibreOffice Calc or Mi¢ro$oft Excel™ to add up each column, and you can compare the column totals, like this maybe

action	Adam	Emily	Harry	Kayla
Adam adapts	2
Emily eats		7
Harry harrumphs			1
Kayla kicks				8
Emily investigates		2
Harry gets a haircut			8
totals	2	9	9	8

But wait; this replaces one problem with another. What if the next scene/whatever you want to add is about Adam running into some issue: 5 pages, but you inadvertently add his #pages to the wrong column?

action	Adam	Emily	Harry	Kayla
Adam adapts	2
Emily eats		7
Harry harrumphs			1
Kayla kicks				8
Emily investigates		2
Harry gets a haircut			8
Adam breaks a leg		5
totals	2	14←oops	9	8

Here's an idea: this spreadsheet is on a computer, right? Aren't computers good at things like putting a number into a particular column, depending on the contents of another spreadsheet cell?

After taking way too long to figure this out, I thought I'd share with you what I did: I created a spreadsheet that looks like the first figure above, where we populate the #pages column. Then, consulting multiple web-search results, constructed formulas to copy the numbers in the #pages column into the appropriate cells in the "Adam", "Emily", etc. columns, depending on the contents of the action column. In the following table, the numbers under Adam, Emily, etc., are all computed by the spreadsheet:

action	#pages	Adam	Emily	Harry	Kayla
Adam adapts	2	2
Emily eats	7		7
Harry harrumphs	1			1
Kayla kicks	8				8
Emily investigates	2		2
Harry gets a haircut	8			8
totals	28	2	9	9	8
	total→	28

As a bonus, we can add up the "Adam", "Emily", etc., pages; if the sum of all those columns matches the sum of the #pages column, that suggests that maybe each of those #pages numbers matched exactly one of "Adam", "Emily", etc.; we didn't miss any and we didn't double-count any. (We could miss by mis-spelling someone's name, and we could double-count by inadvertently including someone's name in the middle of a word. "Kayla reviews Madame Bovary" for example could match both "Adam" and "Kayla"; or if you wrote "Emily and Adam play squash" as one of the actions.) The spreadsheet uses a case-insensitive search to match the character names. There is probably an easy way to make that case-sensitive, but I'll leave that as an exercise for the reader.

Here's how you can use it.

1. Download the libreoffice/openoffice file or the Excel™ file
2. Replace "Adam", "Emily", etc., by your novel's (principal?) characters' names. If you have more than four characters:
   1. Put additional names to the right of Kayla in row 1
   2. Select C2–F2 and extend rightward to G2, H2, etc. to accommodate your additional characters
   3. Select B8–F8 and extend rightward to G8, H8, etc. to accommodate your additional characters
3. Delete rows 3–6 (leave "Harry gets a haircut" as a placeholder; you'll see why shortly)
4. Insert a dozen or a hundred rows before the "Harry gets a haircut" placeholder row, and populate columns C3–H103 (or however far to the right) by extending C2–H2 (or however…) downward to cover the new rows.
5. As you insert action cells (start by replacing "Adam adapts") and fill in the corresponding value in the #pages column, the totals in the totals row should automagically update, because that row has cells that SUM everything from row B ("Adam adapts") up to and including the "Harry gets a haircut" row.
   [If I had planned further ahead, I might have called that row "leave empty for expansion" or something]
6. To make the error-checking thing work for your expanded spreadsheet, ensure that the cell to the right of "total→" is the sum of the previous row's columns B–H (or however…)