New Year, New Spreadsheet?

One of the nerdier things about me is how often I use spreadsheets to track things. 

My books going back to 2013? Obviously

Sales in the housing market in my area, including time on market and about +/- asking price? You bet

Nap and feeding info for the goblin, including how long it would take to get him to go to sleep and what we were doing before he went to sleep in the hopes of figuring out the magic answer to getting him on a sleep schedule that worked? Yes (Did it work? Nope!)

So naturally, when I saw someone post about a bookish bullet journal in excel, I thought hell yeah, of course I'll do this. I mean, at least until I forget about it for a long enough period of time that I can't backfill the data. But for now, let's live in the present and just a few days into the year, I am on top of things. 

What is a bookish bullet journal you may be asking? From the 3 second video I saw (which I can't find anymore), it basically looks like this, though of course there isn't much filled in yet

With the idea, as best as I can figure, to mark down what days I'm reading each book. Each month will have its own color and by the end of the year I'll have a pretty chart that shows me when I was reading. I don't know how much it will tell me about me reading, except perhaps how much of a lag there is when I'm reading a physical copy of a book vs listening, but we'll see. 

The one piece I am pretty proud of is the counting the total number of days spent reading. Because the options are write in numbers in the colors but that would really ruin the vibe. Or else just manually count the squares but I don't want to do that. So after spending way too long considering the problem, I realized I could just write the number 1 in the same color as the background and have the row sum. I don't have to manually count anything but also I keep things aesthetically pleasing. And I get to pat myself on the back.

So there we go. I have something new to add to the month end stats posts, which make up, what 99.99% of the stuff I still post here.

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?