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.

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 an error.  The [SUM_RANGE] argument in square brackets indicates that this portion of the argument is optional, we can use it if we want to add up something other than the range we specified earlier.

For instance, we want to know how many undergraduates there are in the state of Texas. We have studied a variety of means of answering this question in previous chapters. Now, we want to use our conditional SUM function to return this value.

1. Open your College Scorecard Data Excel file. (You can download a fresh copy from here.)
2. Insert a few additional rows above your data table. Three or four will do.
3. Considering you have over 120 columns in this data set, you can select, right-click and hide columns you do not use for the moment.
4. Convert your range into an Excel table so that your formula will use your field names (column headings)
5. Click into any of the cells above your the first few columns and start on your formula by typing in a = sign.
6. Now, you need to specify to Excel that you want it to look at the range of State Abbreviations (STABBR), use TX as your criteria,  then SUM up values in the column showing the Number of Undergraduates (UGDS). Try this on your own, or use the Figure below. Note how the Excel table structure makes it easy to see which column you are referencing, as opposed to using range references.
   

   

7. Note the color-coded borders around each active range in the formula.
8. Now, go ahead, and check the answer using other methods of getting a correct answer. This way, you can confirm that your formula has done its job. You can create an Excel table and filter it for Texas, then use the Total Row, to SUM up the Number of Undergraduates. You can also create a PivotTable using the State Abbreviation and the Number of Undergraduates fields to return an output. Do your answers match?
9. Do a few more variations on this question based on cities, states, regions, types of LOCALE and more!

=COUNTIF(range, criteria)

The COUNTIF syntax starts with an equal sign, followed by our function, and then within the parentheses, we must tell Excel what is the range of values we want it to count based on which criteria.  As with SUMIF, 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 an error.  We do not have an optional argument as we did with SUMIF.

We want to know how many higher educational institutions there are in the state of Texas. Let’s use some of the blank cells we inserted into our sheet above our Excel table earlier to find out!

1. Click into any of the cells above your the first few columns and start on your formula by typing in a = sign.
2. Now, you need to specify to Excel that you want it to COUNT. Use the range of State Abbreviations (STABBR) and TX as your criteria. Try this on your own, or use the Figure below. Note how the Excel table structure makes it easy to see which column you are referencing, as opposed to using range references.
   

   

3. Note the color-coded borders around each active range in the formula.
4. Once again, go ahead, and check the answer using other methods of getting a correct answer. This way, you can confirm that your formula has done its job. You can filter your Excel table for Texas, then use the Total Row, to COUNT the Institution Names or the State Abbreviations. You can also create a PivotTable using the State Abbreviation and the Institution Names fields to return an output. Do your answers match?
5. Do a few more variations on this question based on cities, states, regions, types of LOCALE and more!

=AVERAGEIF(range, criteria, [average_range])

The AVERAGEIF syntax will behave similarly to SUMIF. Within the parentheses, we must tell Excel what is the range of values that have our criteria,  then in the optional argument in the brackets, we tell which range we want it to average if needed. For instance, we want to know what the average % of students enrolled in Business programs in Texas. Let’s use some of the remaining blank cells we inserted into our sheet above our Excel table earlier to find out!

1. Click into any of the cells above your the first few columns and start on your formula by typing in a = sign.
2. Now, you need to specify to Excel that you want it to AVERAGE. Use the range of State Abbreviations (STABBR) and TX as your criteria. Try this on your own, or use the Figure below. Note how the Excel table structure makes it easy to see which column you are referencing, as opposed to using range references.
   

   

3. Note the color-coded borders around each active range in the formula.
4. Once again, go ahead, and check the answer using other methods of getting a correct answer. This way, you can confirm that your formula has done its job. You can filter your Excel table for Texas, then use the Total Row, to COUNT the Institution Names or the State Abbreviations. You can also create a PivotTable using the State Abbreviation and the Institution Names fields to return an output. Do your answers match?
5. Do a few more variations on this question based on cities, states, regions, types of LOCALE and more!

ATTRIBUTION

Chapter 8.3 by Emese Felvegi is licensed under CC BY 4.0