Excel-sheet tracking: I'm a nerd

I've used Google spreadsheets to track my reading for a few years now. It's how I'm able to keep track of those exciting stats I post every month. I also spend a lot of time using Excel at work. Like yes, I have dreamed about spreadsheets before. And since I've recently learned about the magic of conditional calculations (please see post title re: nerd) I thought I'd share with you what I'm doing. Because it's super cool and all that

First thing, I have a different spreadsheet for each year. I've thought about combining them, but I've found each year I tend to add more categories that I'm tracking and really don't feel like going back and retagging old content. That and I'm rarely going through to do year over year comparisons (although yeah, that might happen in the future...) so keeping them separated by year isn't an issue and then there's less I have to look at at any given time.

In one worksheet, I set up whatever columns I want to track. For 2015 I've got:
Title
Author
Non/Fiction
Genre
Pages
A (for author) Race
A Nationality
A Gender
Format (ebook, paperback, etc.)
Translation (Y/N)
Reread
Classic
Review book (as in, I have accepted in exchange for a review)
Readalong
Resolution (does it meet at least one of the qualifications)
Year Published
Decade Published
Month Completed

Yes, this did need a screenshot

Then I've set up 2 more worksheets: one to show me totals by month and the other to show me totals for the year. Here's where those conditional formatting comes in.

I used to just manually count things up and fill in the second worksheet but obviously that leads to errors because counting is hard. I actually was teaching myself all these formulas for work because, well, it's just easier to make it so people don't HAVE to be able to math themselves. And since I spent all that time learning these formulas, why not apply them (or at least a way simplified version of them) to my own book tracking.

Counting using a conditional formula (COUNTIF)
Setting up the formulas for the year is slightly easier, so we'll start there. Let's say I want to show the total number of books written by US authors. I want to have the system automatically count each time I've listed US as the A-Nationality. To do that I need to use a COUNTIF formula, which looks like this:
=countif(Sheet1!G:G,A11)
That is saying it should count all of the cells on Sheet1column G IF the cell says the same thing it says in A11, which in this case says US.

You can see in rows 11-18 I've listed the other Author Nationalities I've read. Now all I have to do is grab the little blue square in the lower right corner of the cell and it will automatically update the calculation for all of those other cells.

I just repeat that for all of my other categories and BOOM, year-to-date totals.

Summing using a conditional formula (SUMIF)
Now let's say I want it to tell me how many pages I've read each month. I use a similar formula as the COUNTIF, except this time I want it to add all the pages, so I do a SUMIF.
=sumif(Sheet1!$R:$R, B1,Sheet1!$E:$E)

This is saying if on Sheet1 Column R (which on Sheet1 is Month Completed) says whatever is in cell B1 (in this case, "January") then I want you to sum the total from column E (which is number of pages).

You'll notice I put $ before the columns in the formula. That's so when I drag the formula over to calculate for all the other months, the formula knows to keep looking at columns R and E. I didn't include it in the purple condition, because I want it to change, so that it will know to look at C1 to show me the totals for February, etc.

Counting using a conditional formula with multiple criteria (COUNTIFS)

There's one last formula I use when I want to calculate the totals for the other categories per month. I need to use a conditional formula that allows be to specify multiple criteria. Let's say I want to know how many books I've read each month that qualify as a resolution book. Then I need to use a COUNTIFS formula

=countifs(Sheet1!$R:$R,B1,Sheet1!$O:$O,"Yes")

With this formula I'm saying that it should count (not sum) the number of times Column O (Resolution) on Sheet 1 says "Yes" only if the value of Column R (Month Completed) on Sheet 1 matches the value in B1 ("January").

Again, repeat for each criteria and I have my totals for each month.

So there you go. That's how I get my totals each month. So cool, right?