47 8.3 Conditional Functions
Emese Felvegi; Noreen Brown; Barbara Lave; Julie Romey; Mary Schatz; Diane Shingledecker; and Robert McCarn
We have used functions beginning with Chapter 2 to return values based on mathematical and statistical functions like SUM, AVERAGE, and COUNT. In Chapter 3 we studied how to set up logical tests, have Excel evaluate our conditions on all items in our ranges, and then process all the values for us. In Chapter 5, we studied how to interpret directions, set up parameters and filter our data depending on a variety of criteria using Excel tables, filters, and slicers.
In this chapter, we continue our work with the College Scorecard dataset we were introduced to in Chapter 6. There, we used PivotTables and PivotCharts to summarize our dataset to gain insights into parameters that describe the cost of education and its return, enrollment trends in different types of programs, the breakdown of the student body, and more. In this chapter, we will look at alternative means of reaching the same answers by using a combination of Excel functions and formulas, we have studied in the earlier chapters.
Table 1 below provides an overview of the functions we will use to return values in Excel. Notice the pattern from SUM to SUMIF to SUMIFS, the combination of SUM + IF, or the combination of SUM + IFS, IF in the plural if you wish. The structure of these functions implies that we will SUM things up IF they meet one condition or criterion. Alternatively, we will SUM things up using the IFS ending when they must meet multiple conditions or criteria. The pattern in the composition of the functions is the same across our core mathematical and statistical functions. AVERAGE and COUNT both have so-called conditional versions that allow us to set parameters to base averaging or counting our values based upon.
| SUM | Adds values you enter in the formula. |
| SUMIF | Adds values that meet a single criterion. |
| SUMIFS | Adds all values that meet multiple criteria. |
| AVERAGE | Calculates the arithmetic mean of a range of values. |
| AVERAGEIF | Returns the average of all cells that meet a single criterion. |
| AVERAGEIFS | Returns the average of all cells that meet multiple criteria. |
| COUNT | Counts the number of cells that contain numbers. |
| COUNTIF | Counts cells using a single criterion. |
| COUNTIFS | Counts cells using multiple criteria. |
Table 1: Mathematical and Statistical Functions.
=SUMIF(range, criteria, [sum_range])
SUMIF essentially asks: what do you want to add up, based on what criteria. The SUMIF syntax starts with our function, then within the parentheses, we must tell Excel what is the range of values (text or numbers, blanks will be ignored) we want it to add up based on which criteria. Our criteria may be a single value like the number 42, or “>42″, the cell reference for where our criterion is located. If you use text or =, >, >=, etc. operators, then make sure to encase them between ” “, or Excel will return