To help illustrate how countif() can be used to generate useful statistics, I’ve created a example.
This example is for a library which has librarians teaching library instruction classes to a variety of group types. The first sheet has the information on who taught which classes on which day, and the second sheet has a couple of tables using countif() to summarize this information.
The beauty of this setup is that the tables are automatically updated whenever a new class is recorded. As long as you record the information in the first sheet, you don’t need to do any adding up. Furthermore, it’s easy to see if you’ve made a mistake in entering an instructor’s name or something — if the totals at the bottom of the “By Instructor” chart don’t match the totals at the bottom of the “By Month” chart, something’s wrong.
If you look at the example you can also see where I’ve used some of the things I’ve talked about in previous posts. For example:
- I used concatenate to tell countif() what to count. That way if I add another librarian, or a whole new category of students, I can just copy the cells next to it instead of having to manually update the formulas.
- I also used concatenate to give countif() something to count, by combining data from two columns into a single one. Without that, and lacking countifs(), you could count how many classes a librarian had taught, or how many had been taught in a certain month, but you couldn’t break it out by student type.
- I used a variety of cell formatting options, including borders around the tables, backgrounds on the total columns in the tables and on the formula columns in the data page, merged cells for the table header, and font changes all over the place.
- I changed the names of the worksheets to make it easier to see at a glance what they held.
- I used the month() formula to figure out what month each class was taught in, so they could be grouped by month. I haven’t yet discussed the month(), year(), and day() formulas, but I plan to.
- I did not hide the first column in the “summary” sheet, but I could have. It needs to exist because the monthly data is recorded as a number, and so must be looked up as a number. However, it looks better to have the names of the months as the row labels in the table than to have just the numbers (Jan, Feb, Mar instead of 1, 2, 3). So it’s off to the side of the table, and could be hidden if you wanted the page to look cleaner.