In This tutorial we’ll review the different ways you need to set up your criteria when using the SUMIFS function in Excel.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here I have a list of six different regions, three different dates and the values associated with each:
The data has 100 rows of information. We are going to use the SUMIFS function to total the values based on the criteria of date and region. Let’s take a look at what the SUMIFS function does and what the syntax is:
So you first enter the range you want Excel to add, then list the first range of data that you want to evaluate with the criteria for that range next. Then the second range to be evaluated and that criteria, and so on for as many sets of criteria as necessary up to 127 sets.
In the first example, I want to sum the values based on the different regions and dates, resulting in this chart:
The formula I used in cell G2 is:
Notice that I used mixed cell references for the two criteria ($F2 and G$1) so that when I copied the formula over and down it automatically adjusted for the region and date.
In the second scenario, I want to break down the values into only two region groups: less than or equal to 3 and greater than three. That resulted in this chart:
The formulas for the two lines of data are:
=SUMIFS($C$2:$C$101,$B$2:$B$101,G$9,$A$2:$A$101,”< =3") =SUMIFS($C$2:$C$101,$B$2:$B$101,G$9,$A$2:$A$101,">3″)
Notice here that the criteria for the region in the first line is “< =3" and the for second line is ">3″. I had to put those criteria in quotes to let Excel know how to calculate the requirements. That is the case when you have specific criteria that you need to evaluate for the SUMIFS function.
In the next example, I want the same result as above, however, I want to refer to a cell rather than hardcode the number into the formula which allows me to change that criteria by entering a different value into the cells in column “F”. The formulas I used in the two rows of the chart are:
=SUMIFS($C$2:$C$101,$B$2:$B$101,G$13,$A$2:$A$101,”< ="&$F14) =SUMIFS($C$2:$C$101,$B$2:$B$101,G$13,$A$2:$A$101,">“&$F14)
Notice here that when referring to a cell rather than a specific number, I put the reference characters in quotes (e.g. “< =") and then concatenated that using an ampersand with the cell that contains the value for the criteria - “< ="&$F14
In the last example, I want to sum the values when they fall between two values. You can’t create one criteria to accomplish this, so you need to add an additional set or criteria to get the correct result, as follows:
The formulas I used in the two rows are:
=SUMIFS($C$2:$C$101,$B$2:$B$101,G$17,$A$2:$A$101,”>=”&$E18,$A$2:$A$101,”< ="&$F18) =SUMIFS($C$2:$C$101,$B$2:$B$101,G$17,$A$2:$A$101,">=”&$E19,$A$2:$A$101,”< ="&$F19)
Here I had to have two sets of criteria to deal with the need to have the values be in between two numbers. One set used the region column “A” to have it greater than or equal to $E18, and the other is to be less than or equal to $F18.
So, those are the various ways you may need to refer to the criteria in Excel when using SUMIFS. Take a few minutes to watch the video so you can see how these formulas are set up. Make sure you have your criteria structured properly so you can get Excel to give you the correct results.
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!