Home / Business & Management / Big Data & Analytics / How to Use Microsoft Excel for Data Analysis / Comparing Year-Over-Year Values Using the SUMIFS Function
Comparing Year-Over-Year Values Using the SUMIFS Function
.
Welcome back. In this part, we’re going to attempt a new approach to creating reports and summaries of the data we have that is not going to start with a pivot like all our previous attempts before. There are a few reasons to do that. One of them is actually the same reason we use the reference to the pivot in the previous part. And we see– we saw that referencing the pivot has its issues. So let’s see– I do start with a pivot because I wanted to show you that the method I’m using actually is working correctly. And the method I’m going to use is using a function in Excel called Sum IFs.
So Sum IFs is a sum function that has multiple criteria for the sum. And parameters for this functions are– the first one is the range that they want to summarise. And the same– the range– and so the criteria all come from the detailed table, which is called Sales Data. So I start to write Sales Data, the moment I start writing sales, Excel shows me the list of options of objects like functions and ranges and tables that have started with this word Sale. And I see that there’s only one. I tab. I write open bracket of open square bracket and I see the list of all the columns in this table.
And the one I’m interested in to summarise is the Revenue. So I start typing R and immediately there’s only one option. Tab, close parentheses, square, and I’m done. Now the criteria. Sales, open brackets, I start to write the word year and tab out. And the value that I want to show you is 2015. Another criteria is sales data. Show the list and I want to see countries. So I write C-O limited to this To tab column. And I want to show, let’s say, Germany. Another criteria that I want to use is the customer bucket, the one that is actually in the philtre right now.
So I say, again, Sales Data, Customer– actually, I see that I misspelled it to custom bucket. But it’s too late now to change. Doesn’t matter much. So the bucket and the value is the one we saw there, 21. There’s 30.
So I’m doing this as like a test to show you that I’m getting the same number. So you see here the number 689572.5 is exactly the value that I have here. Only that now here, it’s rounded to one digit. So it’s– we can see that using the Sum IFs we get the same value for Germany in 2015 for customers, which are between the ages of 21 to 30, exactly the same value. So it seems like a valid way to summarise the data. So let’s delete it. We’ll create it again. And now I want to create the structure of the report.
So an easy way to do that is to start with a pivot, copy this area of the pivot go here and just say, Paste Values. So now I have the values that I want, the years and the countries. But the numbers we’re going to recreate using this method, the Sum IFs. So we’ll see it again. Sum IFs. Not Sum IF, but Sum IFS in plural because we want multiple criteria. Starting with Sales Data. The revenue is the range that we are going to summarise. First criteria is Sales Data, year. And now I’m going to reference the headers of this little region here, which is here. This is the header for the year.
I need to make it so that it always will come from– instead of saying H4 it has to fix the row and let the column be able to drag into to adjust. So by clicking F4 a few times, so I’m going to from– to the value of H dollar 4, which is exactly what I want. H is relative and the 4 is absolute. Continuing, Sales Data, country.
And the value for the country comes from the same row I’m in but from the previous column which is g. So it’s always going to be dollar g and five. And five would be relative so it would be able to break it down. And the last one is– sales data customer bucket. And here I’m going to reference the cell that has the actual– from the philtre of the pivot. The pivot will play a small role in this example here. So I’m going to reference it and I want to reference it absolutely. So it’s $B $1. And I’m done. And I see that the value I get for Australia is exactly the same value.
I’m going to format as currency and I see that it’s exactly the same as in the pivot. Now I can drag it down and I can drag it across.
Let me do it different way. Just copy. There’s a slight difference when you reference a table. When you’re reference a table and you move it across, it will actually change from column to column. So if you do a copy-paste it will work better. It’s kind of strange because usually dragging across and copy-paste are the same. But with a table, they behave differently. So now we have exactly the same values as in the– so here in this pivot, I can actually use it but I will just do– I will clear everything from the pivot, leave only the slicer, do a cut of the pivot and paste it here just above the area for the report.
And here you see I have a report that I can philtre it by a value.
The reason that this happened, I just don’t want to skip it. The reason that it became this way is that the pivot, by default, will adjust the width of the columns it resides on to fit the values. Now see there’s Autofit. This in the options for the pivot. Right click Options, there’s Autofit column. But uncheck the Autofit, this will not happen. And I don’t want the pivot to control the widths of the columns. I want to fix the widths of the columns the way I want and not the way that people thinks it should be. So now I can change values. And so now it’s easy for me to, for example, create a growth column, remember, like we did before.
This is not a pivot anymore. It just regular formulas and everything behaves like formulas. And I have total control. So that’s a good way to create this report. Now the disadvantage is that they are– I don’t have drill down, drill up. I cannot do expand, collapse. It’s difficult for me to add nesting fields on rows or columns and so on. But, on the other hand, everything is under control and it’s very good. If I want to, for example, introduce an empty line here, no problem. I can do it. And this is something I would not be able to do in a pivot.
In a pivot, you cannot insert a new column or a new row in the middle to make it more visible or for any other reason. So many people find that pivots are great for analysis but not so great for reporting. And this method is could be a better way for reporting. So either creating a pivot and referencing it or replacing it altogether with this combination of expressions. But let me show you one more thing. What will happen if I go to All, select the– everything breaks– just is empty. The reason is that now, this last reference to the customer bucket is referencing a value of all in parentheses, which is not something that exists in the actual table.
The table doesn’t have All. It has different specific values. But I can actually work it out. I can say that I can actually be sensitive to this value. And I can say that the value for the customer bucket will be IF dollar H dollar 3 equals, and I will just say parenthesis, all. If it’s that, then use the value asterisks. Asterisks is kind of a wild card value. And if not use, whatever is there. I need to add one more closing parenthesis.
At least it’s fixed one. Now I will go copy it here, paste it here. And now you see that I can go between All and specific value, no problem, just because I was sensitive to the option of this value becoming All. And I replaced All with the wild card asterisk to catch all the values. So in this part, we saw an alternative way to pivot tables for summarising data. We use the Sum IF function. We saw how to create the Sum IF function in one cell, referencing headers coming from rows and from columns, paying attention to the absolute versus relative. We saw how we can reference, also, the philtre of a pivot.
And in the philtre of a pivot, being able to change between values. And we even saw how to take care of the All value, which is a special value, by referencing it directly in the expression. And in this way, we can create very flexible reports that are nicely formatted and still are taking the data directly from the detailed data that we have. See you next time.
Now that you’re familiar with the comparison of data using the pivot table, let’s look at the SUMIFS function in Excel and how it can be used to compare year-over-year data, and better present and analyse your data.
SUMIFS is a function used to sum cells that meet multiple criteria and can be used to sum values when adjacent cells meet criteria based on dates, numbers and text.
The SUMIFS syntax relies on the sum and criteria ranges:
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Some important information to remember:
- The sum range is the range that you want to summarise. This would typically be a table or your pivot table.
- You can reference row and column headings to indicate your criteria ranges.
- You can use relative, absolute and mixed references in your syntax.
- You can reference the filter of your pivot table and change between values in your filter.
- You can reference ‘All’ values, a special value used in the syntax, or a specific value.
For more information on the SUMIFS function, read through the following additional resources:
Join the discussion
To SUMIF or to SUMIFS? Let us know your thoughts on the difference between the SUMIF and SUMIFS functions in Excel. Do you have any tips and tricks for using these functions that you’d like to share?Use the Discussion section below and let us know your thoughts. Try to respond to at least one other post and once you’re happy with your contribution, click the Mark as Complete button to move on to the next step.
This article is from the online course:
How to Use Microsoft Excel for Data Analysis
Created by
This article is from the free online
How to Use Microsoft Excel for Data Analysis
Reach your personal and professional goals
Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.
Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.
