Calculations are a common task for data analysts. In this part of the course, you’ll explore formulas, functions, and pivot tables in spreadsheets and queries in SQL, all of which will help with your calculations. You’ll also learn about the benefits of using SQL to manage temporary tables.

Learning Objectives

---

• Describe the use of functions to conduct basic calculations on data in spreadsheets
• Discuss the use of pivot tables to conduct calculations on data in spreadsheets
• Demonstrate how to use SQL queries to complete calculations in SQL
• Explain the importance of the data-validation process for ensuring accuracy and consistency in your analysis
• Discuss the use of SQL queries to manage temporary tables
• Reflect on how conditional statements can be used to create complex queries and functions
• Generate multiple points of summary based on a wide variety of conditions using COUNTIF, SUMIF, MAXIF, and AVERAGEIF

Functions with multiple conditions

Conditional functions and formulas perform calculations according to specific conditions. In addition, functions including SUMIF and COUNTIF only work in cases where there is one condition. However, if you have more than one condition, you would need to use the SUMIFS or the COUNTIFS function instead. These functions enable you to perform calculations if you have two or more conditions. In this part of the reading, you will learn more about conditional functions and how to construct functions with multiple conditions by exploring their basic syntax and checking out an example. You will also be able to access resources for similar functions in Excel.

SUMIF to SUMIFS

The SUMIF function adds values in a particular range based on a single condition. The basic syntax is =SUMIF(range, criterion, sum_range).

The first range is where the function will search for the condition that you have set. The criterion is the condition you are applying, and the sum_range is the range of cells that will be included in the calculation. For example, in an accounting spreadsheet, you could use SUMIF to calculate the total expenses for a specific category, like Travel expenses, within a given month.

Or, you could find the total sales for automotive fuel treatment products– in this table, the ProductA is high octane fuel and ProductB is standard octane. Table 1 includes columns for Product, Region, Quarter, and Sales.

Table 1

You could use SUMIF to calculate the total sales for Product A using a formula like this:

=SUMIF(A2:A8, “ProductA”, D2:D8)

But, you could also build in multiple conditions by using the SUMIFS function. SUMIF and SUMIFS are very similar: They add up values in a range. But SUMIFS can include multiple conditions. This gives you more control over your summing criteria, which, in turn, allows you to perform more complex data analysis easily.

The basic syntax is: =SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])

The square brackets let you know that this is optional. The ellipsis at the end of the statement enables as many repetitions of these parameters as needed. For example, if you wanted to calculate the sum of sales for ProductA in the East district in the first quarter, you could create a SUMIFS statement with multiple conditions, like this:

=SUMIFS(D2:D8, A2:A8, “ProductA”, B2:B8, “East”, C2:C8, “Q1”)

In this example, B2:B8 is the second criterion_range and East is the second condition. The third criterion_range is C2:C8 and the third condition is Q1. As long as you follow the basic syntax, you can add up to 127 conditions to a SUMIFS statement!

COUNTIF to COUNTIFS

Just like the SUMIFS function, COUNTIFS allows you to create a COUNTIF function with multiple conditions. The definition for COUNTIF is a function that counts the number of cells in a range that meet a single condition. For example, using COUNTIF to track the number of days an temporary employee was absent in an attendance record.

The basic syntax is: =COUNTIF(range, criterion)

Just like SUMIF, you set the range and then the condition that needs to be met. For example, in Table 1, if you wanted to count the number of transactions for ProductA, you could use a COUNTIF function like this:

=COUNTIF(A2:A8, “ProductA”)

COUNTIFS has the same basic syntax as SUMIFS: =COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, …])

The criteria_range and criterion are in the same order, and you can add more conditions to the end of the function. So, if you wanted to find the number of sales transactions for ProductA in the East region in the first quarter, you could use COUNTIFS to apply those conditions, like this:

=COUNTIFS(A2:A8, “ProductA”, B2:B8, “East”, C2:C8, “Q2”)

This enables you to find every instance where both of conditions (East and Q1) are true.

For more information

SUMIFS and COUNTIFS are just two examples of functions with multiple conditions. They help demonstrate how multiple conditions can be built into the basic syntax of a function. There are other functions with multiple conditions that you can use in your data analysis, and many resources available online to help you get started:

• How to use the Excel IFS function: This includes an explanation and example of the IFS function in Excel. It’s a great reference if you’re interested in learning more about IFS. The example is a useful way to understand this function and how it can be used.
• VLOOKUP in Excel with multiple criteria: Similar to the previous resource, this resource goes into more detail about how to use VLOOKUP with multiple criteria. Being able to apply VLOOKUP with multiple criteria will be a useful skill, so check out this resource for more guidance on how you can start using it on your own spreadsheet data.
• INDEX and MATCH in Excel with multiple criteria: This resource explains how to use the INDEX and MATCH functions with multiple criteria. It also includes an example, which demonstrates how these functions work with multiple criteria and actual data.
• Using IF with AND, OR, and NOT functions in Excel: This resource combines IF with AND, OR, and NOT functions to create more complex functions. By combining these functions, you can perform your tasks more efficiently and cover more criteria at once.

Elements of a pivot table

Previously, you learned that a pivot table is a tool used to sort, reorganize, group, count, total, or average data in spreadsheets. In this reading, you will learn more about the parts of a pivot table and how data analysts use them to summarize data and answer questions about their data.

Pivot tables make it possible to view data in multiple ways in order to identify insights and trends. They can help you quickly make sense of larger data sets by comparing metrics, performing calculations, and generating reports. They’re also useful for answering specific questions about your data. 

A pivot table has four basic parts: rows, columns, values, and filters. The rows of a pivot table organise and group data you select horizontally.

The columns organise and display values from your data vertically. Similar to rows, columns can be pulled directly from the data set or created using values. Values are used to calculate and count data. This is where you input the variables you want to measure. This is also how you create calculated fields in your pivot table. As a refresher, a calculated field is a new field within a pivot table that carries out certain calculations based on the values of other fields

Finally, the filters section of a pivot table enables you to apply filters based on specific criteria—just like filters in regular spreadsheets!

Being able to use all four parts of the pivot table editor will allow you to compare different metrics from your data and execute calculations, which will help you gain valuable insights.

Using pivot tables for analysis

Pivot tables can be a useful tool for answering specific questions about a dataset so you can quickly share answers with stakeholders. For example, a data analyst working at a department store was asked to determine the total sales for each department and the number of products they each sold. They were also interested in knowing exactly which department generated the most revenue.

Instead of making changes to the original spreadsheet data, they used a pivot table to answer these questions and easily compare the sales revenue and number of products sold by each department.

Use pivot tables in analysis

In this reading, you will learn how to create and use pivot tables for data analysis. You will also get some resources about pivot tables that you can save for your own reference when you start creating pivot tables yourself. Pivot tables are a spreadsheet tool that let you view data in multiple ways to find insights and trends. 

Pivot tables allow you to make sense of large data sets by giving you tools to easily compare metrics, quickly perform calculations, and generate readable reports. You can create a pivot table to help you answer specific questions about your data. For example, if you were analyzing sales data, you could use pivot tables to answer questions like, “Which month had the most sales?” and “What products generated the most revenue this year?” When you need answers to questions about your data, pivot tables can help you cut through the clutter and focus on only the data you need. 

Create your pivot table

Before you can analyze data with pivot tables, you will need to create a pivot table with your data. The following includes the steps for creating a pivot table in Google Sheets, but most spreadsheet programs will have similar tools. 

First, you will open the Insert menu from the toolbar; there will be an option for Pivot table.

Generally, you will want to create a new sheet for your pivot table to keep your raw data and your analysis separate. You can also store all of your calculations in one place for easy reference. Once you have created your pivot table, there will be a pivot table editor that you can access to the right of your data.

Using your pivot table for analysis

You can perform a wide range of analysis tasks with your pivot tables to quickly draw meaningful insights from your data, including performing calculations, sorting, and filtering your data. Below is a list of online resources that will help you learn about performing basic calculations in pivot tables as well as resources for learning about sorting and filtering data in your pivot tables.

Perform calculations

Microsoft Excel	Google Sheets
Calculate values in a pivot table: Microsoft Support’s introduction to calculations in Excel pivot tables. This is a useful starting point if you are learning how to perform calculations with pivot tables specifically in Excel.	Create and use pivot tables: This guide is focused on using pivot tables in Google Sheets and it provides instructions for creating calculated fields. This is a quick how-to guide you can save and reference as a quick reminder on how to add calculated fields.
Pivot table calculated field example: This resource includes a detailed example of a pivot table being used for calculations. This step-by-step process demonstrates how calculated fields work, and provides you with some idea of how they can be used for analysis.	All about calculated field in pivot tables: This is a comprehensive guide to calculated fields for Google Sheets. If you are working with Sheets and are interested in learning more about pivot tables, this is a great resource.
Pivot table calculated fields: step-by-step tutorial: This tutorial for creating your own calculated fields in pivot tables is a really useful resource to save and bookmark for when you start to apply calculated fields to your own spreadsheets.	Pivot tables in Google Sheets: This beginner’s guide covers the basics of pivot tables and calculated fields in Google Sheets and uses examples and how-to videos to help demonstrate these concepts.

Sort your data

Microsoft Excel	Google Sheets
Sort data in a pivot table or PivotChart: This is a Microsoft Support how-to guide to sorting data in pivot tables. This is a useful reference if you are working with Excel and are interested in checking out how filtering will appear in Excel specifically.	Customize a pivot table: This guide from Google Support focuses on sorting pivot tables in Google Sheets. This is a useful, quick reference if you are working on sorting data in Sheets and need a step-by-step guide.
Pivot tables- Sorting data: This tutorial for sorting data in pivot tables includes an example with real data that demonstrates how sorting in Excel pivot tables works. This example is a great way to experience the entire process from start to finish.	How to sort pivot table columns: This detailed guide uses real data to demonstrate how the sorting process for Google Sheet pivot tables will work. This is a great resource if you need a slightly more detailed guide with screenshots of the actual Sheets environment.
How to sort a pivot table by value: This source uses an example to explain sorting by value in pivot tables. It includes a video, which is a useful guide if you need a demonstration of the process.	Pivot table ascending and descending order: This 1-minute beginner’s guide is a great way to brush up on sorting in pivot tables if you are interested in a quick refresher.

Filter your data

Microsoft Excel	Google Sheets
Filter data in a pivot table: This resource from the Microsoft Support page provides an explanation of filtering data in pivot tables in Excel. If you are working in Excel spreadsheets, this is a great resource to have bookmarked for quick reference.	Customize a pivot table: This is the Google Support page on filtering pivot table data. This is a useful resource if you are working with pivot tables in Google Sheets and need a quick resource to review the process.
How to filter Excel pivot table data: This how-to guide for filtering data in pivot tables demonstrates the filtering process in an Excel spreadsheet with data and includes tips and reminders for when you start using these tools on your own.	Filter multiple values in pivot table: This guide includes details about how to filter for multiple values in Google Sheet pivot tables. This resource expands some of the functionality that you have already learned and sets you up to create more complex filters in Google Sheets.

Format your data

Microsoft Excel	Google Sheets
Design the layout and format of a PivotTable: This Microsoft Support article describes how to change the format of the PivotTable by applying a predefined style, banded rows, and conditional formatting.	Create and edit pivot tables: This Help Center article provides information about how to edit a pivot table to change its style, and group data.

Pivot tables are a powerful tool that you can use to quickly perform calculations and gain meaningful insights into your data directly from the spreadsheet file you are working in! By using pivot table tools to calculate, sort, and filter your data, you can immediately make high-level observations about your data that you can share with stakeholders in reports. 

But, like most tools we have covered in this course, the best way to learn is to practice.

Series Navigation

<< Course 5: Analyse Data to Answer Questions, Module 3: Aggregate data for analysisCourse 6: Share Data Through the Art of Visualisation, Course Overview plus Module 1: Visualise Data >>

Tagged data analysis, google data analytics