It’s that time of year again–where I’m using some downtime to churn out books like a madman.
Many of my projects are collaborative efforts, working with peers–and each of us specializing in a particular part of the content development.
We all know that Excel makes the world go round (arguably, it’s one of the most important pieces of business software to ever be created). It’s used for everything from spreadsheets to project plans to my kids’ art:
Background
Today, though, I wanted to use it to track completion percentages (from a page count perspective) for an updated version of an exam prep book that I’m working on with my coauthor-in-crime, Yura Lee.
This is the actual spreadsheet that we’re using. It’s not totally up-to-date yet (spending more time writing than updating the spreadsheet), but it’s good to see what this looks like in action.
Example
Let’s take a simplified view of the data.
A | B | C | D | E | F | |
1 | Chapter | Author | Est Page Count | Draft Complete | Tech Review Complete | Final Edits Complete |
2 | Intro | Aaron | 12 | X | X | X |
3 | Cloud Services | Aaron | 22 | X | ||
4 | Core Components | Aaron | 29 | X | ||
5 | Comparing Core Services | 20 | ||||
6 | Modern Management | Yura | 30 | |||
7 | M365 Apps | Yura | 26 | |||
8 | TOTAL | 139 |
Column A contains the chapter name. Column B contains the author name. Column C contains the number of pages in the chapter, and then placing an X in Columns D, E, or F indicates that we’ve completed that work.
There are two similar Excel functions that can be useful here in tallying results:
Which one you use depends on how many arguments you need to compare.
SUMIF
The SUMIF
function is really useful if you want to evaluate the data in just two columns. For example, I just want to compute how many pages each author has to complete individually. This requires just the following data:
- Author column (range in column B, such as
B2:B7
) - Author name value (text value, such as
Aaron
orYura
) - Number of pages per chapter (range in column C, such as
C2:C7
)
To express this as a query, we’d enter the following formula in an empty cell:
=SUMIF(B2:B7,"Aaron",C2:C7)
This translates to: “For every cell in B2 through B7, check to see if it matches the text value “Aaron.” If it does, add the corresponding cell from column C.
Lather, rinse, repeat for as many author (or other lookup) values as you have. If you’re following along in Excel with this data set, you should get 63.
Cool, cool.
SUMIFS
Writing a tech book is a big job and has lots of stages–rough drafts, a technical review where the accuracy of technical concepts or code is checked, and an editorial review where grammar, formatting, and voice are checked.
I also wanted to be able to monitor how we’re progressing through the stages. Not only do I want to know how many pages each of us have completed, but I’m also interested in how we’re tracking against each of the stages of delivery (draft, tech review, final edits).
For this, we need to check more than the values in two columns. Enter SUMIFS
.
SUMIFS
works on a similar lookup principal as SUMIF, but we’re able to add extra conditions for evaluation. Using our data sample above, you can enter this in a blank cell to see how far a particular author is in each stage.
=SUMIFS(C2:C7,D2:D7,"=X",B2:B7,"Aaron")/C8
There’s a lot going on, so let’s break it down:
- =SUMIFS( – Calls the
SUMIFS
function. - C2:C7,D2:D7,”=X”, – For every cell in the first range (
C2
throughC7
), check to see if the corresponding cell in the second range (D2
throughD7
) contains the value “X”. If so, flag that evaluation as true. - B2:B7,”Aaron”) – If the evaluation of the cell pair from columns C and D (for example,
C2
andD2
) came back true, then check to see if the corresponding cell in the third range (B2
throughB7
) matches the text valueAaron
. If so, then add the numerical value from the corresponding cell in C to the running tally. - /C8 – Divide the result by the value in
C8
, which results in a percentage.
You can also think of it this way:
If cell D2
has the value of X
and cell B2
has the value of Aaron
, add the value in C2
. Using the sample data, I am 100% done with drafts, but 0% done with tech review and final editing.
Maybe if I wasn’t so busy blogging, I would get it done.
Cheers!