The SUM function in Excel is probably the most widely used function available. In fact it is the only formula that has its own keyboard shortcut (Alt & =). Therefore it should come as no surprise that this basic mathematical function has more powerful cousins available in Excel. These functions are know as SUMIF and SUMIFS.
Lets say you wanted to sum a column in your spreadsheet by a specific criteria. Taking the example in the image below, lets say you wanted to sum the sales for just blue items. Using the formula =SUM(D2:D14) will result in a total for all sales. So we would need to use the function SUMIF.
<img src="https://theexcelclub.com/wp-content/uploads/2017/03/excelsum-300x187.png" width="400" height="249" /><br/>
SUMIF will allow you sum one column based on criteria selected in another column. In our example we can see that we want to sum column D and our criteria is in column B. The syntax for SUMIF is (=Range, Criteria, Criteria Range). Where range is the range in which you want to sum, in this case column D. The criteria is "Blue" (note how this is in "" as it is text. If you are using a cell reference there is no need to use ""). The criteria range is the range in which you will find the criteria, in this case column
<img src="https://theexcelclub.com/wp-content/uploads/2017/03/excelsumif-300x183.png" alt="Sumif and SUMIFS in excel" width="400" height="244" /><br/>
But what if you wanted to sum based on two or more criteria. Our data set also contains Region, we could try and get the total sales for Blue in the North. SUMIF will only allow you sum based on one criteria. SUMIFS will allow you sum using multiple criteria.
The syntax for SUMIFS is (=Sum Range, Criteria Range 1, Criteria 1, [Criteria range 2, Criteria 2)....). As you can see this function requires the range in which you want to sum as the first range, after this you then select the cells that contain the criteria and then you specify what the criteria is. After this you can add the second and subsequent criteria.
In this video we will look at an example of SUMIFS in action. Its one thing talking about a function in Excel, but if you really want to learn and understand it, have a look at the video and then set your self out with a simple example.
Just before we get to the video, here is another excel tip. Lets say you wanted to set your self up with some random sample data. The quickest way is to select the cells in which you want to include the data and then enter the formula =RANDBETWEEN(bottom, top). For bottom and top enter the lowest and high-test number you want in your sample data. Then press CTRL & Enter to populate all of the cells.
<h5><center><em><strong>Updated* Now includes XLOOKUP</strong></em></center></h5>
<center><strong>The Ultimate Excel Formulas Course Excel 365</strong></center></h5>
<a href="https://theexcelclub.newzenler.com/courses/the-ultimate-excel-formulas-course-excel-365" target="_blank" rel="noopener noreferrer" data-elementor-open-lightbox="">
<img src="https://theexcelclub.com/wp-content/uploads/2019/09/excel-365-formulas-course.png" sizes="(max-width: 428px) 100vw, 428px" srcset="https://i0.wp.com/theexcelclub.com/wp-content/uploads/2019/09/excel-365-formulas-course.png?w=428 428w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2019/09/excel-365-formulas-course.png?resize=254%2C300 254w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2019/09/excel-365-formulas-course.png?resize=410%2C485 410w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2019/09/excel-365-formulas-course.png?resize=100%2C118 100w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2019/09/excel-365-formulas-course.png?resize=275%2C325 275w" alt="" width="428" height="506" /><br/> </a>
<a role="button" href="https://theexcelclub.newzenler.com/courses/the-ultimate-excel-formulas-course-excel-365">
Explore The Ultimate Excel Formulas Course
</a>
<p><center><strong>BEST VALUE EXCEL AND EXCEL POWER TOOL LEARNING</strong></center></p>
<center><strong>ACCESS ALL AREAS, UNLIMITED LEARNING FOR ONE ANNUAL SUBSCRIPTION</strong></center></p>
<a href="https://theexcelclub.newzenler.com/courses/access-all-areas" data-elementor-open-lightbox="">
<img src="https://theexcelclub.com/wp-content/uploads/2019/09/access-all-areas.png" sizes="(max-width: 438px) 100vw, 438px" srcset="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/09/access-all-areas.png?w=438 438w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/09/access-all-areas.png?resize=262%2C300 262w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/09/access-all-areas.png?resize=410%2C470 410w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/09/access-all-areas.png?resize=100%2C115 100w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/09/access-all-areas.png?resize=275%2C315 275w" alt="" width="438" height="502" /><br/> </a>
<a role="button" href="https://theexcelclub.newzenler.com/courses/access-all-areas">
Preview Access All Areas
</a>
https://www.youtube.com/watch?v=qxTX5wEkRZE
<h2><center>In return for this tip on SUMIF and SUMIFS in Excel, I ask that you share this post or the video with your friends and colleagues</center></h2>
<h4><center><strong>Sign up for my newsletter. Don’t worry, I wont spam. Just useful Excel and Power BI tips and tricks to your inbox</strong></center></h4>
<center></center></h4>
<a role="button" href="https://theexcelclub.newzenler.com/f/email-signup">
NEWSLETTER SIGN UP
</a>