In Excel SUMIF and SUMIFS functions, the order of arguments is different. While the common part is clear - similar destination and parameters - the differences are not so obvious, though very essential.

To use them correctly, you need to clearly understand what these two functions have in common and in what way they are different. 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. =SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2) Using SUMIFS and SUMIF in Excel - things to remember In this case, you won't have to change the formula to calculate the quantity of other fruit from a different supplier: To refine the formula further, you can replace the text criteria 'apples' and 'Pete' with cell references. Now assemble the above parameters, and you will get the following SUMIFS formula: =SUMIFS(C2:C9, A2:A9, 'apples', B2:B9, 'Pete') 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' When you're learning something new, it's always a good idea to start with simple things.

What you want is to find out a sum of amounts relating to a given fruit and supplier, e.g. You have the fruit names in column A, suppliers' names in column B, and quantity in column C. Suppose, you have a table listing the consignments of fruit from different suppliers. Important! The SUMIFS function works with AND logic, meaning that each cell in the sum_range argument is summed only if all of the specified criteria are true for that cell.ДЄnd now, let's have a look at the Excel SUMIFS formula with two conditions. You can use up to 127 range/criteria pairs in SUMIFS formulas. For example you can use criteria such as 10, '>=10', A1, 'cherries' or TODAY().Д¬riteria_range2, criteria2, - these are additional ranges and criteria associated with them, optional. You can supply the criteria in the form of a number, logical expression, cell reference, text or another Excel function. Only cells with numbers are summed blank and text values are ignored.Д¬riteria_range1 - the first range to be evaluated by the associated criteria, required.Д¬riteria1 - the first condition that must be met, required. This can be a single cell, a range of cells or a named range. Sum_range - one or more cells to sum, required. The first 3 arguments are mandatory, additional ranges and their associated criteria are optional. The SUMIFS function was introduced in Excel 2007, so you can use it in all modern versions of Excel 2016, 2013, 20.Д¬ompared to SUMIF, the SUMIFS syntax is a little bit more complex: SUMIFS(sum_range, criteria_range1, criteria1, ) You use SUMIFS in Excel to find a conditional sum of values based on 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. And still, we say that Excel SUMIF can be used to sum values with multiple criteria. sum_range - the cells to sum if the condition is met, optional.ДЄs you see, the syntax of the Excel SUMIF function allows for one condition only. criteria - the condition that must be met, required. Range - the range of cells to be evaluated by your criteria, required.

We discussed its syntax in detail in the previous article, so let me give you just a quick summary now. The SUMIF function is used to conditionally sum values based on a single criteria.

Excel 2007 tutorial 19