When you are working with date-stamped data imports, the volume of information can be overwhelming. It is difficult to tell, at a glance, whether performance indicators are trending upwards, downwards, speeding up, or slowing down. Aggregate statistics like Week-To-Date, Month-To-Date, and Year-To-Date performance data can often be easier to read and tell you more about what’s actually going on. Let’s learn how to implement X-To-Date aggregate statistics using helper columns, Excel date functions, and SUMIFS.

Examine the Data

We seem to be looking at a basic date series. There’s a row for each day with a date stamp, a list of tasks  available and tasks completed. We already have a % Completed column that is re-calculated for each day.

What are Helper Columns?

We are going to add Week-To-Date, Month-To-Date, and Year-To-Date columns to this spreadsheet. To make the process simpler and make the formulas easier to understand, we’re going to use a number of helper columns to spread out the work and simplify the logic involved.

A helper column is a column that calculates a number for use in other formula cells. It’s called a “helper” column, because while it helps get the result you need in the final column, it’s usually not useful by itself. When we are done with this exercise, we are going to hide the helper columns to make the spreadsheet easier for other people to read.

Adding Helper Columns for Week-To-Date

How does a Week-To-Date calculation work? It starts at the beginning of the week and adds up all the rows that occur in the same week of the same year, up until the current day. In order to calculate a Week-To-Date column, we need to be able to compare the week that each row occurs in and the day of the week for each row. We’ll also check to make sure the dates appear in the same year. Fortunately, Excel has functions for just those purposes. WEEKDAY() calculates the day of the week. WEEKNUM() calculates how many weeks the date is into the current year. YEAR() calculates the year of the date. The syntax for these functions is as follows:

=WEEKDAY(serial_number, [return_type])

=WEEKNUM(serial_number, [return_type])

=YEAR(serial_number)

The serial_number is the way that Excel stores the date. You can specify any cell formatted like a date.

The [return_type] lets you tell Excel when your week begins. 0 (zero) is a standard Sunday – Saturday week. 1 is Monday – Sunday. 2 is Tuesday – Monday, etc.

In cell E2 create a formula to calculate the weekday of the date in A2. We’re going to start our weeks on Monday. The formula will look like this:

=WEEKDAY(A2,2)

In cell F2 create a formula that calculates the week number of the date in A2. Again, the week will start on Monday. That formula will look like this:

=WEEKNUM(A2,2)

Finally, we’ll calculate the year of the date in cell G2. The formula will look like this:

=YEAR(A2)

Drag down the formulas to populate the rest of the cells in columns E:G. The output should resemble this:

Calculating Week-To-Date using SUMIFS

Now that we have the helper columns in place, we can assemble a SUMIFS function to add up the tasks achieved for each week. The syntax for SUMIFS is as follows:

=SUMIFS(sum_range, criteria_range1, criteria1, ...)

The sum_range field is the set of cells you want SUMIFS to add together if they meet the criteria.

The critera_range1 field is the set of cells you want to test your first IF statement against.

The criteria1 field is the conditional statement you want to test the criteria_range1 against.

SUMIFS can take as many sets of criteria_range and criteria as necessary. The sum_range will be column C.

To sum the tasks achieved each week, we’ll need three sets of criteria tests – one for each of the helper columns we just built.

The first criteria_range will be column E. To see if the weekday is equal to or less than the current row, we need to set the criteria to be the WEEKDAY of the date in cell A2.

The second criteria_range will be column F. To see if the week # matches the current row, we need to set the criteria to be the WEEKNUM of the date in cell A2.

The third criteria_range will be column G. To see if the year matches the current row, we need to set the criteria to be the YEAR of the date in cell A2.

The final SUMIFS statement will look like this:

=SUMIFS(C:C,E:E,"<="&WEEKDAY(A2,2),F:F,WEEKNUM(A2,2),G:G,YEAR(A2))

That takes care of the tally of the Achieved Tasks, but we still need to calculate the number of total Tasks for each calculation. Fortunately, we don’t need to do all this work all over again. Select the SUMIFS statement we just created and copy everything but the equal sign at the beginning. The only thing we will be changing is the sum_range. The SUMIFS formula for the second half of the calculation will look like this:

=SUMIFS(B:B,E:E,"<="&WEEKDAY(A2,2),F:F,WEEKNUM(A2,2),G:G,YEAR(A2))

We need to combine the statements, dividing the total Achieved Tasks by the total Tasks to get the ratio we need for the percentage. The final formula in cell H2 is as follows:

=SUMIFS(C:C,E:E,"<="&WEEKDAY(A2,2),F:F,WEEKNUM(A2,2),G:G,YEAR(A2))/
 SUMIFS(B:B,E:E,"<="&WEEKDAY(A2,2),F:F,WEEKNUM(A2,2),G:G,YEAR(A2))

Drag down the formula to populate the rest of the cells in column H. The output should look like this:

Now, we’re done with the first 2 helper columns, so we can hide them. Select the labels for columns E and F. Right click the selection and choose Hide.

 

Adding Helper Columns for Month-To-Date

The process for building helper columns for the Month-To-Date column is very similar to the Week-To-Date process.

Month-To-Date starts at the beginning of the month and adds up all the rows that occur in the same month of the same year, up until the current day. In order to calculate a Month-To-Date column, we need to be able to compare the month that each row occurs in and the day of the month for each row. We’ll also check to make sure the dates appear in the same year. The Excel function MONTH() calculates the month of the date. DAY() calculates the day of the month. We’ll use the same column of YEAR calculations in column G. The syntax for the new functions is very straightforward:

=MONTH(serial_number)

=DAY(serial_number)

The serial_number (as before) is the way that Excel stores the date. You can specify any cell formatted like a date.

In cell I2 create a formula to calculate the month of the date in A2. The formula will look like this:

=MONTH(A2)

In cell J2 create a formula that calculates the week number of the date in A2. That formula will look like this:

=DAY(A2)

Drag down the formulas to populate the rest of the cells in columns I:J. The output will look like this:

Calculating Month-To-Date using SUMIFS

Now that we have the helper columns in place, we can assemble a SUMIFS function to add up the tasks achieved for each month. The process is the same as for Week-To-Date.

The first criteria_range will be column I. To see if the month is equal to the current row, we need to set the criteria to be the MONTH of the date in cell A2.

The second criteria_range will be column J. To see if the day is equal to or less than the current row, we need to set the criteria to be the DAY of the date in cell A2.

The third criteria_range will be column G. To see if the year matches the current row, we need to set the criteria to be the YEAR of the date in cell A2.

The first SUMIFS statement will look like this:

=SUMIFS(C:C,I:I,MONTH(A2),J:J,"<="&DAY(A2),G:G,YEAR(A2))

We’ll do the same trick to shift the sum_range for the second SUMIFS function. The second SUMIFS statement will look like this:

=SUMIFS(B:B,I:I,MONTH(A2),J:J,"<="&DAY(A2),G:G,YEAR(A2))

We need to combine the statements, dividing the total Achieved Tasks by the total Tasks to get the ratio we need for the percentage. The final formula in cell K2 is as follows:

=SUMIFS(C:C,I:I,MONTH(A2),J:J,"<="&DAY(A2),G:G,YEAR(A2))/
 SUMIFS(B:B,I:I,MONTH(A2),J:J,"<="&DAY(A2),G:G,YEAR(A2))

Drag down the formula to populate the rest of the cells in column K.

Now, we’re done with the Month and Day helper columns, so we can hide them. Select the labels for columns I and J. Right click the selection and choose Hide.

Adding a Helper Column for Year-To-Date

We have one more helper column to build for Year-To-Date

Year-To-Date starts at the beginning of the year and adds up all the rows that occur in the same year, up until the current day. In order to calculate a Year-To-Date column, we need to be able to compare the year that each row occurs in and the day of the year for each row. The Excel function YEARFRAC() calculates the percentage of the year that has passed since a starting date. DATE() calculates the day in Excel format, given the month, day, and year. We’ll use it to help calculate the year fraction. We’ll use the same column of YEAR calculations in column G. The syntax for the new functions is as follows:

=YEARFRAC(start_date, end_date, [basis])

=DATE(year, month, day)

The start_date defines the beginning of your year – your year can start arbitrarily and doesn’t need to begin January 1st.

The end_date is the end of the period you wish to calculate as a fraction of a year.

The [basis] lets us define the “year” in a few different ways. 1 will give us the actual year.

The DATE function takes a numerical year, a numerical month between 1-12, and a day between 1-31.

In cell L2 create a formula to calculate the fraction of the year that has passed as of the date in A2. The formula will look like this:

=YEARFRAC(DATE(G2,1,1),A2,1)

We’re telling YEARFRAC to start the year at January 1, of the year in cell G2, and tell us how far along in the year cell A2 is.

Drag down the formula to populate the rest of the cells in column L. The output will look like this:

Calculating Year-To-Date using SUMIFS

We only need 2 criteria for the Year-To-Date calculation.

The first criteria_range will be column G. To see if the year is equal to the current row, we need to set the criteria to be the YEAR of the date in cell A2.

The second criteria_range will be column L. To see if the fraction of the year is equal to or less than the current row, we need to set the criteria to be the Year Fraction of the date in cell A2.

The first SUMIFS statement will look like this:

=SUMIFS(C:C,G:G,YEAR(A2),L:L,"<="&L2)

We’ll do the same trick to shift the sum_range for the second SUMIFS function. The second SUMIFS statement will look like this:

=SUMIFS(B:B,G:G,YEAR(A2),L:L,"<="&L2)

We need to combine the statements, dividing the total Achieved Tasks by the total Tasks to get the ratio we need for the percentage. The final formula in cell M2 is as follows:

=SUMIFS(C:C,G:G,YEAR(A2),L:L,"<="&L2)/SUMIFS(B:B,G:G,YEAR(A2),L:L,"<="&L2)

Drag down the formula to populate the rest of the cells in column M.

Now, we’re done with the rest of the helper columns, so we can hide them. Select the labels for columns G and L. Right click the selection and choose Hide.

 Aggregate Statistics X-To-Date Example

You can play around with the example before the data was aggregated on the first worksheet and after on the second sheet. You can also download the sample file by clicking the Excel icon in the bottom right.