Using SUMIFS() to track completion percentage

5/5 - (3 votes)

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 or Yura)
  • 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 through C7), check to see if the corresponding cell in the second range (D2 through D7) 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 and D2) came back true, then check to see if the corresponding cell in the third range (B2 through B7) matches the text value Aaron. 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!

author avatar
Aaron Guilmette
Helping companies conquer inferior technology since 1997. I spend my time developing and implementing technology solutions so people can spend less time with technology. Specialties: Active Directory and Exchange consulting and deployment, Virtualization, Disaster Recovery, Office 365, datacenter migration/consolidation, cheese.