Learn how to use the Excel functions SUMIF and SUMIFS

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] - The second range of cells where the [criteria2] is applied against. This argument is optional.

  • [criteria2] - The criteria to use on [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:

  1. 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)

  1. Once done entering the formula, press ENTER.

  2. 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