In Microsoft Excel and Google Sheets, there are two main ways of summarising data: SUMIFs and pivot tables. SUMIFs allow you to summarise data based on a condition, while pivot tables allow you to summarise data by row and column. In this article, we will look at how to use SUMIFs and pivot tables to organise and analyse campaign data.
What are Pivot Tables and SUMIFs?
Pivot Table
A pivot table is a data summarization tool that allows you to quickly and easily create tables and graphs from large amounts of data. Pivot tables are especially useful for extracting summarised information from a data set, such as spend totals, product categories, and audience demographics. Additionally, pivot tables can be used to create charts and graphs to help visually represent the data.
SUMIFs
SUMIFS is a function in Excel and Google Sheets that allows you to sum the values in a given range of cells, based on specific criteria that you set. In order to use the SUMIFS function, you first need to define the range of cells that you want to sum, and then specify the criteria for which you want to sum the values. The SUMIFS function can be used to sum values based on one or more criteria.
Pivot Tables versus SUMIFs
When it comes to working with data, there are two main ways to summarise it: SUMIFs and pivot tables. Both have their pros and cons, but which is the best tool for the job?
To start with, SUMIFs are good for summarising data that is already in a table. You can use them to calculate totals or averages for a range of cells, based on a condition. For example, you could find the total clicks for campaigns with 10,000 impressions by using the SUMIF function.
However, SUMIFs can only be used for limited types of data analysis. They are also not very flexible, so you can’t always get the results you want using this method.
Pivot tables offer more flexibility and power when it comes to data analysis. They are great for summarising data by row or column. You can use them to calculate totals, averages, and other statistics. They are also easy to use: all you need to do is drag and drop the fields you want to summarise.
In the end, it depends on what you need to do with your data. If you need to summarise it, then a pivot table is the way to go. If you need to build more advanced reports or gather deeper insights, then SUMIFs will do the trick. However, it is very useful to have both within your arsenal as a digital marketer.
Download Free SUMIFs and Pivot Table Template
If you are working on Google Sheets, make a copy of the template under File > Make a copy.
If you are working on MS Excel, download as Microsoft Excel (.xlsx) file.
If you are entirely new to Pivot tables and/or SUMIFs, we recommend to look through the following to learn how to create pivot tables on Google Sheets and Excel.
| Tool | Instructions |
| Pivot Table | 1) Insert tab > Create pivot table 2) Under Table/Range field: drag the entire data set. In this sample, it’ll be $A$1:$I$23. 3) Choose where you want your pivot table to be placed. It is usually a new worksheet. If not, select the cell where you want this to be. |
| Formula (SUMIFs) | 1) Simply select a cell and type =SUMIFs(sum_range, criterion_range1, criterion1, criterion_range2, criterion2, etc.) |
Analysing Campaign Data with Pivot Tables and SUMIFs
In most cases, you might use both rather than one over the other. Here are some scenarios when we might use either or both.
Use Case Scenario #1: Reporting high level data
It is more straightforward to use pivot tables to look at data at a higher level. For example, if you have spend, click and impression figures for different countries, you can use pivot tables to see how each country is performing individually and compare their performance against each other. From there, you can make the call to optimise your campaign based on Tactic.
See Sheet “PIVOT TABLES” and refer to Row 53.
Use Case Scenario #2: Trended data over time
Trending data is important for keeping track of changes over time. A pivot table can help you visualise and analyse data in a concise way. A trended data pivot table makes it easy to see the changes that have occurred over time. SUMIFs do not really help fulfill that as the output is usually a single value.
There’s no trended data in this template, but you may refer to this guide here.
Use Case Scenario #3: Comparing data
Assuming you want to compare 2 dimensions while looking at multiple values, a pivot table might be more suitable. All you need to do is simply drag and drop and respective dimensions and values and you get the output you need. SUMIFs allow you to compare data as well but the initial set up might be more tedious.
See Sheet “PIVOT TABLES” and refer to Row 31 & Row 43. Note that for aggregated figures such as CTR (click-through rate), be careful not to
Use Case Scenario #4: Visualising data
The thing about pivot charts on Excel is that it is not flexible – it usually visualises everything in the table. As a result, we may end up creating a pivot table for just one chart. This is because every time we rearrange the fields on pivot chart, it will affect the table as well.
On Google Sheets, you can choose the rows and columns you want to want to include the chart. Thus, you just need one pivot table to create multiple charts. Depending on which software you use, using SUM/SUMIFs would give you more flexibility to organise your data for visualising purposes. You can also use them with other data sets or formulas if you need.
See “SUMIFs + Charts” for a simple chart sample that makes use of SUMIFs. To understand SUMIFs better, you may refer to
Use Case Scenario #5: Dashboarding
Similar to #4, relying formulas like SUMIFs can make your report more dynamic. It can even be used as a dashboard – something that your teammates or yourself can use and pull data from the get go. It can also be easily recycled as long as the columns remain the same. However, if you are just looking at simple filters to cut your data, pivot tables would do the trick just as fine. On another level, you may even combine both utilities to enhance your dashboard!
See “SUMIFs + Dropdown” to see how you can cut the data by using SUMIFs to get the result you need.
In Summary (pun intended)
Pivot tables are a great way to summarise data, while SUMIFs are better for specific calculations. Pivot tables are easy to use and can be created in a matter of minutes. They are perfect for quickly summarising data, whether it is for a presentation or for further analysis. SUMIFs are more complicated to use, but they can be powerful tools for calculating data. If you need to perform specific calculations on your data, then SUMIFs are the tool for you.
Attribution
Image by StellrWeb via Unsplash