CoverAcknowledgementsExcel OverviewTime SaverAuto Fill and Fill SeriesCell ReferencingAbsolute and Relative ReferencingNavigation ShortcutsManaging CellsSorting and FilteringHiding and GroupingPaste SpecialNamed RangesRankingRoundingSum and CountManaging WorksheetsLookup Functions Data Validation IF Statements Formula Errors Text FunctionsText to ColumnsLogic Functions 2Date and TimeConditional Formatting 1Standards of Professionalism Spreadsheet Formatting and Design Basic ChartsDashboardsConditional Formatting 2Spreadsheet Protection Basic Pivot TablesAdvanced Pivot Tables and Slicers Logic Functions 3Advanced Lookup FunctionsLoan Amortization Schedule Advanced ChartsMacros 1Macros 2Macros 3How to Use Search Engines

Logic Functions 3

This chapter will explore advanced logic or conditional functions in Excel, including CountIF, SumIF, AverageIF, and embedded IF statements in other functions. Each function is capable of evaluating a conditional expression and returning a designated value if true. Furthermore, these functions include a plural variation to analyze multiple criteria conditions to narrow down the result.

Practice Spreadsheet

Use this workbook for the chapter.

The exercises in this chapter will use the dataset in Figure 24.1 which includes employee name, gender, position, tenure, college GPA, and salary. We will add conditional inputs for reference to modify our calculations as needed, and we will have output cells for each function in this chapter.

BUS115_image_24.1.png
Figure 24.1

In previous chapters, if we wanted to find the average salary of employees with a tenure of 10 years, we could use an IF function to check if the employee's tenure is greater than 10 and return their salary if true or nothing if false. We would copy the formula for each employee before using an Average function to evaluate the resulting range of cells.

BUS115_image_24.2.png
Figure 24.2

However, instead of evaluating the condition and calculating the average separately, we can use IF logic functions to do both in one cell and vary the results based on our input cells.

CountIF and CountIFS

The CountIF function can be used to evaluate a range of cells and return a count of items that meet a criteria. In this example, we will count the number of employees of a specific gender from Figure 24.1.

  1. Type =COUNTIF and a left parenthesis ( ( ) to start the function.
  2. Select the range of cells containing the gender data and type a comma ( , ).
  3. Select the input cell containing the gender criteria.
  4. Type a right parenthesis ( ) ) to close the formula.
  5. Press Enter to complete the function (shown as =COUNTIF(C3:C17,J4) in Figure 24.3).
BUS115_image_24.3.png
Figure 24.3
 

The CountIFS function works similarly to CountIF but allows for multiple criteria. If we need to count employees by gender and their college GPA, CountIFS can evaluate both criteria. In this example, we will need to check if GPA is greater than the specified input by including a greater than calculation in the function's formula.

  1. Type =COUNTIFS and a left parenthesis ( ( ) to start the function.
  2. Select the range of cells containing gender data and type a comma ( , ).
  3. Select the input cell containing the gender criteria and type a comma ( , ).
  4. Select the range of cells containing college GPA data and type a comma ( , ).
  5. Type ">" and an ampersand ( & ) to indicate a greater than calculation will be performed.
    1. Note: Double quotation marks must be included around the greater than sign for the calculation to work correctly in this function.
  6. Select the input cell containing the GPA criteria.
  7. Type a right parenthesis ( ) ) to close the formula.
  8. Press Enter to complete the function (shown as =COUNTIFS(C3:C17,J4,F3:F17,">"&J6) in Figure 24.4).
BUS115_image_24.4.png
Figure 24.4
 

SumIF and SumIFS

The SumIF function can be used to calculate the total number of values in a selected range of cells. The formula will prompt for the range of cells to evaluate, the criteria, and an optional range of cells to be added associated with the evaluated range. In this exercise, we want to calculate the total salaries of employees with a tenure greater than a specified number of years in the company.

  1. Type =SUMIF and a left parenthesis ( ( ) to start the function.
  2. Select the range of cells containing tenure data and type a comma ( , ).
  3. Type ">" and an ampersand ( & ) to indicate a greater than calculation will be performed.
  4. Select the input cell containing the tenure criteria and type a comma ( , ).
  5. Select the range of cells containing salary data.
  6. Type a right parenthesis ( ) ) to close the formula.
  7. Press Enter to complete the function (shown as =SUMIF(tenure,">"&t_1,salary) in Figure 24.5).

Note: Cell ranges are named for ease of reference. You may consider naming ranges similarly. However, please note that the names are not required to complete the function successfully.

BUS115_image_24.5.png
Figure 24.5
 

Similar to the CountIFS function, the SumIFS function works like SumIF but allows for multiple criteria conditions. For example, if we need to calculate the total salaries of employees of a specific gender and based on their college GPA, the SumIFS function can check for both criteria and add the salaries of those employees.

  1. Type =SUMIFS and a left parenthesis ( ( ) to start the function.
  2. Select the range of cells containing salary data and type a comma ( , ).
  3. Select the range of cells containing gender data and type a comma ( , ).
  4. Select the input cell containing the gender criteria and type a comma ( , ).
  5. Select the range of cells containing GPA data and type a comma ( , ).
  6. Type ">=" and an ampersand ( & ) to indicate a greater than or equal to calculation will be performed.
  7. Select the input cell containing the GPA criteria.
  8. Type a right parenthesis ( ) ) to close the formula.
  9. Press Enter to complete the function (shown as =SUMIFS(salary,gender,g_1,GPA,">="&gpa_1) in Figure 24.6).
BUS115_image_24.6.png
Figure 24.6
 

AverageIF and AverageIFS

The AverageIF function is used to calculate an average of values that meet a specific criteria. The function prompts for a range to evaluate, criteria, and an optional range to calculate. In this case, we want to know the average tenure of employees that have a tenure below a specified number of years.

  1. Type =AVERAGEIF and a left parenthesis ( ( ) to start the function.
  2. Select the range of cells containing tenure data and type a comma ( , ).
  3. Type "<" and an ampersand ( & ) to indicate a less than calculation will be performed.
  4. Select the input cell containing the tenure criteria.
  5. Type a right parenthesis ( ) ) to close the formula.
  6. Press Enter to complete the function (shown as =AVERAGEIF(tenure,"<"&t_1) in Figure 24.7).
BUS115_image_24.7.png
Figure 24.7
 

Again, the AverageIFS function allows for multiple criteria to be set. In this example, we want to know the average tenure of employees by gender with a GPA greater than or equal to a specified number.

  1. Type =AVERAGEIFS and a left parenthesis ( ( ) to start the function.
  2. Select the range of cells containing tenure data and type a comma ( , ).
  3. Select the range of cells containing GPA data and type a comma ( , ).
  4. Type ">=" and an ampersand ( & ) to indicate a greater than or equal to calculation will be performed.
  5. Select the input cell containing the GPA criteria and type a comma ( , ).
  6. Select the range of cells containing gender data and type a comma ( , ).
  7. Select the input cell containing the gender criteria.
  8. Type a right parenthesis ( ) ) to close the formula.
  9. Press Enter to complete the function (shown as =AVERAGEIFS(tenure,GPA,">="&gpa_1,gender,g_1) in Figure 24.8).
BUS115_image_24.8.png
Figure 24.8
 

Standard Deviation Sample

Not every statistical function includes an IF variation. For example, the standard deviation function (STDEV.S), which calculates the variability around the average of a dataset, does not have an IF logic variation. Instead, we need to embed an IF function within the standard deviation function to perform the calculation based on specific criteria. In this exercise, we'll embed an IF function to determine an employee salary dataset for a standard deviation calculation based on employee tenure greater than a specific number of years.

  1. Type =STDEV.S and a left parenthesis ( ( ) to start the function.
  2. Type IF and a second left parenthesis ( ( ).
  3. Select the range of cells containing tenure data.
  4. Type a greater than sign ( > ).
  5. Select the input cell containing the tenure criteria and type a comma ( , ).
  6. Select the range of cells containing salary data and type a comma ( , ).
  7. Type two double quotation marks ( " ) to indicate a blank output.
    1. Double quotation marks are typically used for a text string. In this case, no text is included, so the function will display a blank result if the logic argument is false.
  8. Type a right parenthesis ( ) ) to close the IF formula.
  9. Type a second right parenthesis ( ) ) to close the STDEV.S formula.
  10. Press Enter to complete the function (shown as =STDEV.S(IF(tenure>t_1,salary,"")) in Figure 24.9).
BUS115_image_24.9.png
Figure 24.9
 

Max and Min Functions

The Max function is another that can use an embedded IF function. If we want to know the maximum GPA of employees with a GPA greater than a specific value, we will need to use an IF formula to filter the results and identify the maximum value from the criteria. 

  1. Type =MAX and a left parenthesis ( ( ) to start the function.
  2. Type IF and a second left parenthesis ( ( ).
  3. Select the range of cells containing GPA data.
  4. Type >= to indicate a greater than or equal to calculation will be performed.
  5. Select the input cell containing the GPA criteria and type a comma ( , ).
  6. Select the range of cells containing GPA data and type a comma ( , ).
  7. Type two double quotation marks ( " ) to indicate a blank output.
  8. Type a right parenthesis ( ) ) to close the IF formula.
  9. Type a second right parenthesis ( ) ) to close the MAX formula.
  10. Press Enter to complete the function (shown as =MAX(IF(GPA>=gpa_1,GPA,"")) in Figure 24.10).
BUS115_image_24.10.png
Figure 24.10
 

The Min function may also use an embedded IF function to determine a minimum value from a filtered dataset. In this example, we want to know the minimum salary of employees that have a tenure greater than a specific number of years.

  1. Type =MIN and a left parenthesis ( ( ) to start the function.
  2. Type IF and a second left parenthesis ( ( ).
  3. Select the range of cells containing tenure data.
  4. Type > to indicate a greater than calculation will be performed.
  5. Select the input cell containing the tenure criteria and type a comma ( , ).
  6. Select the range of cells containing salary data and type a comma ( , ).
  7. Type two double quotation marks ( " ) to indicate a blank output.
  8. Type a right parenthesis ( ) ) to close the IF formula.
  9. Type a second right parenthesis ( ) ) to close the MIN formula.
  10. Press Enter to complete the function (shown as =MIN(IF(tenure>=t_1,salary,"")) in Figure 24.11).
BUS115_image_24.11.png
Figure 24.11
 

The IF function can be embedded in other functions to determine data to be used based on the desired criteria. Remember to use it or the IFS function to narrow the data sample using specific conditions.

Supplemental Resource

End-of-Chapter Survey

: How would you rate the overall quality of this chapter?
  1. Very Low Quality
  2. Low Quality
  3. Moderate Quality
  4. High Quality
  5. Very High Quality
Comments will be automatically submitted when you navigate away from the page.
Like this? Endorse it!