Excel Functions SUMIF and SUMIFS sum up numeric values that meet specified criteria. These criteria can be based on **dates**, **numbers**, or **text**. Criteria based on dates and text must be enclosed with double quotes (i.e. “01/01/2019”). On the other hand, a number will require double quote only if it has operators (i.e “>100”). Otherwise, the double quote is not required. Both functions support logical operators such as “Greater Than” (**>**), “Less Than” (**<**), “Not Equal to” (**<>**), “Equal” (**=**) and wildcards (*****,**?**) for partial matching. Although both functions support calculations based on criteria, Excel SUMIF calculate values based on a single criterion while SUMIFS is based on multiples criteria.

**SYNTAX**

**SUMIF**

**=SUMIF (range, criteria, [sum_range])**

**range - The range of cells where the criteria is applied against.****criteria**– serves as SUMIF criteria used to determine which cells to add. This criterion is applied against the**range**.**[sum_range] - The range of cells to add together. [sum_range] is optional wherein if omitted, the cells in range are added together instead.**

**SUMIFS**

**=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)**

**sum_range -**The range of cells to add together.**criteria_range1**- The first range of cells where the**criteria1**is applied against.**criteria1**- The criteria to use on**criteria_range1**.**[criteria_range2]**-**[criteria2]**is applied against.**[criteria2]**-**[criteria_range2]**. This argument is optional. Note that range and criteria are supplied in pairs and Excel SUMIFS function allows up to 127 pairs of range and criteria.

**BASIC APPLICATION – SUMIF**

*Figure A. SUMIF Function*

Suppose you have a report that shows sales per Agent, including Date and Branch as illustrated in ** Figure A** and you want to derive the total Sales if the Sales Agent is

**Adam**.

Instructions:

Following the syntax

**=SUMIF (range, criteria, [sum_range])**, type below formula into cell D22 where, D5:D16 is the range where our criteria is placed, “Adam” is the criteria and E5:E16 is the [sum_range]. Alternatively, you can highlight ranges D5 to D16 and E5 to E16 in excel while typing the formula and you will get the same result.

**=SUM(D5:D16, “Adam”,E5:E16)**

Once done entering the formula, press

**ENTER**.check if you have highlighted the correct range and criteria, go to cell

**D22**(where your formula or function is located) and press**F2**. This will reveal your formula including the range you have used. In this case,**range**is highlighted in blue while**sum_range**is in red.

**BASIC APPLICATION – SUMIFS**

*Figure B. SUMIFS Function*

On this second example, you have the same data presented in SUMIF function. However, in this case, you require more than one criterion where you want to find the total sales if the Branch is **Branch 1** and the date is **01-Jan-19.**

Instructions:

Following the syntax **=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)**, type below formula into cell D22 where, E5:E16 is the sum_range, B5:B16 is the criteria_range1 where our criteria1 is placed, “Branch 1” is the criteria1, C5:C16 is the [criteria_range2] where the criteria2 is placed and “1/1/2019” is the criteria2 and then press **ENTER.** Alternatively, you can highlight ranges E5 to E16, B5 to B16 and C5 to C16 in excel while typing the formula and you will get the same result.

**=SUMIFS(E5:E16,B5:B16,"Branch 1",C5:C16,"1/1/2019")**

Repeat the same method as indicated in SUMIF function to verify if you have highlighted the correct ranges.

If you would like to read more about this topic, check out the Microsoft guide in this link: https://support.office.com/en-gb/article/sumif-function-169b8c99-c05c-4483-a712-1697a653039b