Last week I was focusing on how to use incremental formulas to determine the most recent time something was used, but in the process of adding that to the example spreadsheet I also ended up making a couple of other changes that are worth discussing.
Change 1: Limited to the current year
You might have noticed that although there were dates from January 2012 through May 2013, on the table showing classes taught by month that there aren’t any entries after May. That’s because I’ve set it to only pull the dates from the current year. Here’s a generic explanation as to do that.
Data setup: Assume you have the following named columns.
Date: The date something happened. In the example, it’s the date the class was taught.
Person: The person that did it. In the example, it’s the librarian who taught the class.
Action: The thing that happened. In the example, it’s the type of class that was taught.
Step 1: Choose the two data pieces you want to compare. In the example, I’ve got a table showing the number of classes taught of each type, each month, so my two data pieces are the class type and the month.
Step 2: Extract the two data pieces, if necessary. In the example I have Date, not Month. So for this step I would add a column named Month, and use the formula =month(Date). I also extracted the first letter of the class type to avoid potential errors due to typos and abbreviations, but for the simplicity of this explanation I’ll pretend I didn’t.
Step 3: Extract the year. In order to limit it by year, you need to get the year out of the Date. So you would want to add another column, name it Year, and use the formula =year(Date).
Step 4: Combine the data using concatenate. You can skip this step if you’re using countifs() instead of countif(). In the example I’m comparing the months and the first initial of the action, and I’m limiting it by year, so the formula would be =concatenate(Year,Month,Action). For an undergraduate class taught on April 21, 2013 the result of this formula would be “20134Undergraduate”. Name this column whatever you want – in this case I’d probably choose “Combo_YrMoAct” for “Combination Year, Month and Action”.
Table setup: Assume you have a table with one of the two data pieces as the column headers, and the other piece as the row headers, and have named them appropriately. In the example, assume that the column headers are named Tbl1_Action, and the row headers are named Tbl1_Month. The Tbl1 stands for Table 1, so if you have another table that also uses the Action as one of the two headers, there won’t be any name conflict.
Table formula: In the body of the table, count the number of times each row/column combination appears in the Combination column.
Step 1: Figure out the matches between the headers and the data columns. In the example, Tbl1_Action matches with the Action column from the source data, and Tbl1_Month matches with the Month column from the source data.
Step 2: Decide how you’re inserting the year. If you always want it to show the data for the current year, no matter how far in the future you open it, use year(today()). If you want it to always show the same year, just enter that year in the formula you’ll build in the next step. Or, if you want to make it easy for you to specify what year you want it to show, make a named cell called “WhichYear” and put the year you want in it. For the rest of this explanation I’ll assume we’re using the third option.
Step 3: Build the formula to count the number of times each combination in the table occurs in the source data.
— If you are using countif(): Use concatenate() to combine the data you want to look up, and countif() to count the number of times it occurs in the Combination column. In the example the concatenate part would be concatenate(WhichYear,Tbl1_Month,Tbl1_Action), so the whole formula would be =countif(Combo_YrMoAct, concatenate(WhichYear,Tbl1_Month,Tbl1_Action)).
— If you are using countifs():Use countifs() to count the number of times each combination appears in the relevant columns of the source data. In the example it would be =countifs(Year,WhichYear, Month,Tbl1_Month, Action,Tbl1_Action).
Step 4: Copy the formula to all of the cells in the body of the table.
That’s it! Now it’s sensitive to what year something happened, even though that’s not an actual variable in the table.