SUMIF:

SUMIF function in Microsoft Excel, adds the cells specified by a criteria.

Syntax for this excel function =Sumif(range, criteria, sum range)

Sumif formula can be used in Microsoft Excel to add all values against a single criteria.

Let’s take an example, here we have a data table of Sales persons and their sales numbers. We are interested in finding the total sum of sales for a sales person named ‘Paul’. We mention the Sales Person name in the cell A2 as Paul and the sumif function in the cell B2 as =Sumif(A6:B12,A2,B6:B12)

Here, A6:B12 is the range, A2 is the criteria and Sum range is from B6:B12.

SUMIFS:

SUMIFS function in Microsoft Excel adds the cells in a range that meets multiple criteria.

Syntax for this functions is SUMIFS (sum range, criteria range1, criteria1, [criteria range2, criteria2, criteria range n, criteria n])

Sumifs formula can be used in Microsoft Excel to add all values against multiple criteria.

Let’s take an example, here the table has Country, Region and the corresponding sale values. We are interested in finding the sum of sales figure for Eastern England. To get this, we used SUMIFS function in the cell C2 as “=SUMIFS (C6:C12, A6:A12, A2, B6:B12, B2)”

Let’s break the Microsoft Excel formula for better understanding. C6:C12 is the sum range from which we need the total sales figure, A6:A12 is the first criteria range and A2 is first criteria (Country as England). B6:B12 is the second criteria range and B2 is the second criteria (Region as East). Finally, we can get the total sales for eastern England as 326.

The difference between DGET and Sumifs is that DGET pulls only one value which can be a text or value where as Sumifs adds all the values which satisfy the criteria.