SUMIF and SUMIFS are two of Excel’s best functions. Using these two functions, you can create formulas that add data, subject to one or more conditions. The primary difference in these two functions is that SUMIF allows only one condition to be evaluated, while SUMIFS allows up to 127 criteria to be evaluated; additionally, SUMIFS is only available in Excel 2007 or higher. In this tip, you will learn how to create advanced SUMIF and SUMIFS formulas that use cell references as the evaluation criteria.
Consider the data shown in Figure 1. Cell B16 contains the following formula: =SUMIF(B2:B15,">1000"), which instructs Excel to sum the data in cells B2 through B15 when the cells are greater than $1,000. The criteria can be a number, an expression, a cell reference, or text. If the criteria are a number or a cell reference, enter it without any quotation marks; if the criteria is an expression or text, it must be entered surrounded by quotation marks.
Figure 1 - Simple SUMIF Formula
Given the above, what if the criteria consist of some type of expression that includes a cell reference? In these cases, the expression must include an ampersand (&) symbol to concatenate the elements together into an expression. To illustrate, consider the data shown in Figure 2. Notice that this data includes a “Sum Threshold” amount in cell E2. Cell B16 includes a SUMIF formula that adds the data in cells B2 through B15 that exceeds the amount shown in cell E2. The syntax of that formula is as follows:
=SUMIF(B2:B15,">"&E2). See that in this example, the greater than sign (>) is surrounded in quotation marks and the reference to cell E2 is preceded by an ampersand symbol (&). Utilizing these techniques, you can create SUMIF formulas that use cell references for variable criteria.
Figure 2 - Advanced SUMIF Formula Including An Expression With A Cell Reference
Extending the example shown above, consider the data shown in Figure 3. In this case, two input criteria exist and because SUMIF is limited to one criterion, we must use SUMIFS in this example to sum the data in B2 through B15 based on the criteria entered in cells E2 and E3. In this example, cell B16 contains the following formula: =SUMIFS(B2:B15,B2:B15,">"&E2,A2:A15,">"&E3). The preceding formula instructs Excel to sum the data in B2 through B15 when the values in B2 through B15 are greater than the value in cell E2 and when the dates in A2 through A15 are greater than the date entered in E3. Note the use of quotation marks and ampersand symbols in this formula. Additionally, note that both conditions must be met in order for the data to be summed.
Figure 3 – Advanced SUMIFS Formula With Multiple Criteria
SUMIF and SUMIFS are relatively simple functions that you can use to generate very powerful results by knowing just a few tricks. By combining operators such as greater than and less than with cell references, you can use SUMIF and SUMIFS to create dynamic results that vary depending upon user input. Begin exploring how these functions can streamline some of your data analysis needs today!
For a video demonstration of this tip, please visit www.tinyurl.com/k2tips105.