In one worksheet, I set up whatever columns I want to track. For 2015 I've got:
A (for author) Race
Format (ebook, paperback, etc.)
Review book (as in, I have accepted in exchange for a review)
Resolution (does it meet at least one of the qualifications)
|Yes, this did need a screenshot|
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:
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.
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.
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
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?