Using functions to make dynamic charts (2)

Introduction 

In the last post, we looked at using the IF function to vary the values for Series 2 of Chart 2 and ensure the images hover above the data bars for Series 1 as the values for Series1 change. In this post, we are going to use the COUNTIFS function in Table 1 to automatically calculate the species we have seen on each day of the week for Chart 1, and then use a simple SUM function in Table 2 to calculate the weekly totals that are supplied to Chart 2.

The COUNTIFS function

The COUNTIFS function is based on the same TRUE/FALSE logical test as the IF function, but adds the flexibility to check whether more than one criteria is met before calculating the result. For example, count how many times Dunnock appears in Column D of Tracker sheet for Monday of this week:

Here's a snapshot of the formula that is one of the cells in Table 1 of our Data sheet:

The first part of the formula is looking at Column C of the Tracker sheet and asking does this equal the value of cell N5 (minus 6) of the Data sheet.The second part of the formula says if the answer is TRUE, then count how many times in Column D the value matches the value in cell N7 of the data sheet. To understand the references in this formula, lets look at the Data sheet, and Table 1:

As you can see, cell N5 contains a date. The date is set at the Sunday of the current week (in this instance, 21/01/2018). In the cell where this formula is entered, we want to calculate the result for Monday, therefore we subtract 6 from Sunday's date which converts the date to 14/01/2018 (for Tuesday, we would subtract 5, for Wednesday we would subtract 4, etc.).

Cell N7 contains the name of the first species we want to check. So, how many times on 14/01/2017 does BLACK CAP appear in our Tracker sheet? The formula returns a count of how many times both logical tests are met.

Referencing

We are going to use this formula across the entire table, so we need to make sure that the cell references are fixed where they need to be, and at the same time, allow us to copy them down the columns and increment the row number for us. For this, we use the $ in the cell references to fix the columns, rows or a mixture of the two where we want them. This is called Absolute (completely fixed), Relative (fully mobile) and Mixed (where the column or the row is fixed, and the other mobile) Referencing. In our example, we see this:

Tracker Column C which contains dates is an absolute (fixed) reference (note that with no row numbers, this reference points to the whole column which will extend to over 1 million rows).

Data N5 is also fixed - we need to always refer to this cell for our date.

Tracker Column D is fixed - this contains our species for a given date.

The very last part of the formula is a mixed reference. Cell N7 of the Data sheet contains the first species in our table that we will check. We'll need to fix the Column N, but allow the row number to increase as we copy the formula down and across out table, so we have a $ preceding N, but not 7:

$N7

 To change cell referencing, place the cursor somewhere in the cell reference (between the N and the 7), and then press the F4 function key on your keyboard. This will put $ before the column and the row reference ($N$7). Press F4 again, and only the row will be fixed (N$7); press F4 again, and only the column will be fixed ($N7). 

As we copy the formula down, each species will be picked up as $N7 becomes $N8, then $N9, etc. If we copy the formula across from N7 to U7 and then check each of those cells, we'll see the formula is identical, as all the column reference as Absolute. This is exactly what we want, and means we only have to type the formula once in cell N7, and copy it across the row. This is what it'll look like:

One final thing to do before we copy the formula down our table columns - decrease the amount subtracted from the value in Data N5 by 1 for each column. As mentioned earlier, the date in Data cell N5 is set for the Sunday of the current week, so for Monday (Column N), the formula is $N$5-6, which gives our formula the correct date for Monday. In Column M7, change the formula to $N$5-5 so that we have Tuesday's date. Continue across the columns until you reach Sunday. In this cell, remove the subtraction, as the value in Data N5 is Sunday's date:

Now we can copy the formulas down their respective columns, and the table will automatically calculate the values in our table as the Tracker sheet is updated:

Table 2

Now that we have a dynamic Table 1, let's put a SUM function in Table 2 so that both Table and Charts are completely self-updating. In Table 2, we simply SUM the values for all the columns in each row:

Notice that none of the references are fixed in this formula; we are only copying it down a single column, and we need the row number to increment. Type the formula in the first cell of the column, and then copy it down to the last - it will automatically increment.

I hope you enjoyed this post - let me know if you have any questions or need any help.

In the next two posts, we are going to look at two very different ways of calculating our Table 1 data by using Pivot Tables, and then a modified SUMPRODUCT function.