This tutorial explains the difference between the SUMIF and SUMIFS functions in terms of their syntax and usage, and provides a number of formula examples to sum values with multiple AND / OR criteria in Excel 2016, 2013, 2010, 2007, 2003 and lower. As everyone knows, Microsoft Excel provides an array of functions to perform various calculations with data. A few articles ago, we explored and, which are designed for counting cells based on a single condition and several conditions, respectively. Last week we covered that adds values meeting the specified criteria. Now it's time to go over the plural version of SUMIF - Excel SUMIFS that allows summing values by multiple criteria. Click 'Search' and allow Windows to find your document for you. If you have not entered keywords, the system will find all Excel files that meet the other criteria, so you may have a long list to scroll through to find your file. Windows Vista Step. Click the 'Start' orb in the lower-left corner of your screen. ![]() Those who are familiar with the SUMIF function might think that converting it to SUMIFS takes just an extra 'S' and a few additional criteria. This would seem quite logical but 'logical' it's not always the case when dealing with Microsoft: ) • • • • • • Excel SUMIF function - syntax & usage The SUMIF function is used to conditionally sum values based on a single criteria. We discussed its syntax in detail in the, so let me give you just a quick summary now. SUMIF(range, criteria, [sum_range]) • range - the range of cells to be evaluated by your criteria, required. • criteria - the condition that must be met, required. • sum_range - the cells to sum if the condition is met, optional. As you see, the syntax of the Excel SUMIF function allows for one condition only. And still, we say that Excel SUMIF can be used to sum values with multiple criteria. How can that be? ![]() By adding the results of several SUMIF functions and by using SUMIF formulas with array criteria, as demonstrated in the examples that follow. Excel SUMIFS function - syntax & usage You use SUMIFS in Excel to find a conditional sum of values based on multiple criteria. The SUMIFS function was introduced in Excel 2007, so you can use it in all modern versions of Excel 2016, 2013, 2010 and 2007. Compared to SUMIF, the SUMIFS syntax is a little bit more complex. SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ) The first 3 arguments are mandatory, additional ranges and their associated criteria are optional. • sum_range - one or more cells to sum, required. This can be a single cell, a range of cells or a named range. Only cells with numbers are summed; blank and text values are ignored. • criteria_range1 - the first range to be evaluated by the associated criteria, required. • criteria1 - the first condition that must be met, required. The SUMIFS function works with AND logic, meaning that a cell in the sum range is summed only if it meets all of the specified criteria (all the criteria are true for that cell). And now, let's have a look at the Excel SUMIFS formula with two conditions. Suppose, you have a table listing the consignments of fruit from different suppliers. You have the fruit names in column A, suppliers' names in column B, and quantity in column C. What you want is to find out a sum of amounts relating to a given fruit and supplier, e.g. All apples supplied by Pete. Outlook for mac 2016 voting buttons. When you're learning something new, it's always a good idea to start with simple things. So, to begin with, let's define all the arguments for our SUMIFS formula: • sum_range - C2:C9 • criteria_range1 - A2:A9 • criteria1 - 'apples' • criteria_range2 - B2:B9 • criteria2 - 'Pete' Now assemble the above parameters, and you will get the following SUMIFS formula: =SUMIFS(C2:C9, A2:A9, 'apples', B2:B9, 'Pete') To refine the formula further, you can replace the text criteria 'apples' and 'Pete' with cell references. In this case, you won't have to change the formula to calculate the quantity of other fruit from a different supplier: =SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2) Using SUMIFS and SUMIF in Excel - things to remember Since the aim of this tutorial is to cover all possible ways to sum values by several conditions, we will discuss formula examples with both functions - Excel SUMIFS and SUMIF with multiple criteria. To use them correctly, you need to clearly understand what these two functions have in common and in what way they are different. While the common part is clear - similar destination and parameters - the differences are not so obvious, though very essential. The order of arguments In Excel SUMIF and SUMIFS functions, the order of arguments is different. In particular, sum_range is the 1st parameter in SUMIFS, but it is 3 rd in SUMIF.
0 Comments
Leave a Reply. |