SUM, SUMIF, and SUMIFS are some of the most used and valuable functions for formulas

Among the many functions and formulas within Excel; there are some that are much more useful than others. The ones I will discuss now (SUM, SUMIF, and SUMIFS) are among them and are also among my favorites.

1. SUM At some time, sooner or later, if you have numerical data; you’re going to want to know what is the total of something you have, what is the total of everything, etc. Formula examples:
   1. Sum within a column:
      1. =SUM(D5,D15) means sum cell D5 & D15
      2. =SUM(D5:D15) means sum D5 through D15
   2. Sum within multiple columns:
      1. =SUM(D5,F5) means sum cell D5 & F5
      2. =SUM(D5:F15) means sum D5 through F15 [which means sum cells: D5 through D15 + E5 through E15 + F5 through F15]

Within some of the following definitions and explanations; I will show the formulas with cell ranges (column [letter] & row [number]) and also with named ranges (which I highly recommend using to make your formulas much easier to understand). In the following examples: D5:D15 = Car Brands; E5;E15 = Car Models; F5:F15 = Amount Sold

2. SUMIF sum by single criteria. Formula example: =SUMIF(D5:D15,”Toyota”,F5:F15) or =SUMIF(Car Brands,”Toyota”,Amount Sold)

1. Formula in Layman’s terms: =SUMIF(range that contains criteria, criteria, range you want to sum)
2. Formula in Excel syntax: =SUMIF(range, criteria, sum_range [optional, see usage notes])
3. Explanation of formula: add the amount in each cell in F5 through F15 that has “Toyota” in cells D5 through D15 [add the amount in each cell in Amount Sold that has “Toyota” in Car Brands]
4. Usage notes (best explained & taken from: https://exceljet.net/excel-functions/excel-sumif-function)
   1. When sum_range is omitted, the cells in range will be summed.
   2. Text criteria, or criteria that includes math symbols, must be enclosed in double quotation marks (“).
   3. Numeric criteria can be supplied without quotation marks.
   4. The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters.
   5. To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*)

3. SUMIFS sum by multiple criteria. Formula example: =SUMIF(F5:F15, D5:D15,”Toyota”, E5:E15,”Camry”) or =SUMIF(Amount Sold,Car Brands,”Toyota”,Car Models,”Camry”)
   1. Formula in Layman’s terms: =SUMIF(range you want to sum, range that contains the 1st criteria, 1st criteria, range that contains the 2nd criteria, 2nd criteria, etc)
   2. Formula in Excel syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], etc)
   3. Explanation of formula: add the amount in each cell in F5 through F15 that has “Toyota” in cells D5 through D15 and “Camry” in cells E5:E15 [add the amount in each cell in Amount Sold that has “Toyota” in Car Brands and “Camry” in Car Models]
   4. Usage notes (best explained & taken from: https://exceljet.net/excel-functions/excel-sumifs-function)
      1. Unlike the SUMIF function, SUMIFS can apply more than one set of criteria, with more than one range. The first range is the range to be summed. The criteria is supplied in pairs (range/criteria) and only the first pair is required. For each additional criteria, supply an additional range/criteria pair. Up to 127 range/criteria pairs are allowed.
      2. Each additional range must have the same number of rows and columns as the sum_range.
      3. Non-numeric criteria must be enclosed in double quotes, but numeric criteria does not need quotes except with operators, i.e. “>32”.
      4. The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters.
      5. To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).
      6. SUMIF and SUMIFS can handle ranges, but not arrays. This means you can’t use other functions like YEAR on the criteria range, since the result is an array. If you need this functionality, use the SUMPRODUCT function.
      7. The order of arguments is different between the SUMIFS and SUMIF functions. Sum_range is the first argument in SUMIFS, but the third argument in SUMIF.