Quarterly Report – Using SUMIFS()

In this post, along with the next few posts, I will be discussing a hypothetical case where data has been provided and your job (as the analyst) will be to create a quarterly report detailing sales of 2018 new vehicles in Canada. The first example will be using the SUMIFS() formula.

The purpose of this post is not to discuss the trend of sales of cars in Canada, but instead to provide methods for creating a quarterly analysis using Excel-based methods.

The Data Set

The data set that we will be using is the 2018 new motor vehicle sales, which is publicly available at Statistics Canada. https://www150.statcan.gc.ca/t1/tbl1/en/tv.action?pid=2010000101

I have made this information available through a .CSV file available on this site:
Car Sales

The top of the data set should look as follows, with the first 2 columns being REF_DATE and GEO.

Relevant columns in the data set for this analysis include:

• REF_DATE – The month where data is being measured.
• GEO – The geographic region (province or country).
• Vehicle type – The type of vehicle being measured (truck or passenger car).
• UOM – Unit of measurement; units or dollar sales.
• Value – The total amount of sales. (in units or thousands of dollars, depending on the unit of measurement)

The Report

The objective of the analysis is to generate the same report as the image below. It must have the following features:

• The report will show the number of units and the percentage of total units sold by vehicle type in a given period.
• Results must be able to be viewed on a monthly, quarterly and annual basis.
• Results must be broken down by both vehicle types, Passenger Cars and Trucks.
• A filter must be available to show results by region.
• Quarterly and total results must be in bold.
• Quarterly and total results must have top and bottom borders.

Now that we know what we are trying to build, it’s time to start building the report!

Step 1 – Import the data into your spreadsheet.

The best way to get the data from the .CSV file is to import using the “From Text/CSV” option under the Data tab.

The CarSales CSV file should be available for importing after you have navigated to the correct directory.

There will be no need to transform any of the data. Click the Load button to load the information into Excel.

This will create a new table named CarSales on Sheet2 with all the data from the CarSales CSV file.

Step 2 – Set up the formatting on Sheet1.

Now that the data has been imported, the next step is to set up the formatting for the final report. Since it is important to get the look and feel of the report set up first, it is better to have this ready before manipulating the data. This is not true in all cases. In some cases, it is worthwhile to explore the data before building the report shell.

The end product of the formatting setup should look exactly like the final report, only without data.

A few things worth noting include:

• The dates should be in the same format as the REF_DATE column in the imported CarSales table. These should not be imported as dates (i.e. 1/1/2018).
• Set the units to Accounting, zero decimal places, and percentage of total to Percentage formatting.
• Make sure to bold the results in the quarterly columns. It can be easy to check the formatting by entering a 1 into every cell in the table.

Step 3 – Creating SUMIFS() formulas for the Units columns.

Since we have a table set up with the underlying data, you can refer to the table instead of having to refer to the range. This way, the table name and fields will fill in as you type them.

• 

• 

For the sum_range, the autofill should pick up the CarSalesTable[VALUE].

The first criterion should be the units of measurement (“UOM”), which will be Units. You can either type Units, or refer to cell B7, making sure to use absolute notation ($B$7).

The second criterion should be the region (“GEO”), which should always refer to cell $B$4. At this point, your formula should look like this:

Next, we refer to the vehicle type (“Vehicle type”), which must refer to row 6 at all times, but will not be anchored to column B so that it can pick up the value of ‘Trucks’ for column D. In this case, use B$6 as your criteria.

Finally, we refer to the date (“REF_DATE”). This anchoring is the opposite of the anchoring for vehicle type, and will always refer to column A, but different rows for each date. In this case, use $A8.

Your formula is now complete for cell B8, January 2018 for passenger cars!

You should now be able to fill in all the unit total rows, as seen in the image below.

Step 4 – Aggregating quarterly values with SUBTOTAL().

While it might be tempting to use a SUM() function to add up the quarterly values, it is better to use a SUBTOTAL(9,[Cells]) formula, like the one seen below.

This formula can be copied into every quarter in columns B and D.
Once this is complete, a SUBTOTAL() can be added for all months in column B and D (including the prior subtotals), reducing the risk of a quarter not being properly captured.

At this point, your report will look as follows:

Step 5 – Using a SUM() for total units.

While you could perform a SUMIFS() to get the value for “Total, new motor vehicles”, it is easiest to calculate the total by just summing up the passenger cars and trucks columns.

Copy that formula down from row 8 to row 24, and you should have everything ready except the percentages.

Step 6 – Fill percentages by dividing months by columns.

Finally, you need to fill in the percentage of the total for both passenger cars and trucks.

This is done by dividing the units column by the total column, making sure to use absolute referencing for column F.

Once these percentages are copied for all cells, your report is complete! Congratulations!

Next Steps

This was only one way to solve this kind of report – in the next post, I will be discussing how to perform the same analysis using a basic pivot table.