In this reading, you will learn more about conditional functions and how to construct functions with multiple conditions. Recall that conditional functions and formulas perform calculations according to specific conditions. Previously, you learned how to use functions like SUMIF and COUNTIF that have one condition. You can use the SUMIFS and COUNTIFS functions if you have two or more conditions. You will learn their basic syntax in Google Sheets, and check out an example.

Refer to the resources at the end of this reading for information about similar functions in Microsoft Excel.

The basic syntax of a SUMIF function is: =SUMIF(range, criterion, sum_range)

The first range is where the function will search for the condition that you have set. The criterion is the condition you are applying and the sum_range is the range of cells that will be included in the calculation.

For example, you might have a table with a list of expenses, their cost, and the date they occurred.

You could use SUMIF to calculate the total price of fuel in this table, like this:

But, you could also build in multiple conditions by using the SUMIFS function. SUMIF and SUMIFS are very similar, but SUMIFS can include multiple conditions.

The basic syntax is: =SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

The square brackets let you know that this is optional. The ellipsis at the end of the statement lets you know that you can have as many repetition of these parameters as needed. For example, if you wanted to calculate the sum of the fuel costs for one date in this table, you could create a SUMIFS statement with multiple conditions, like this:

This formula gives you the total cost of every fuel expense from the date listed in the conditions. In this example, C1:C9 is our second criterion_range and the date 12/15/2020 is the second condition. As long as you follow the basic syntax, you can add up to 127 conditions to a SUMIFS statement!

Just like the SUMIFS function, COUNTIFS allows you to create a COUNTIF function with multiple conditions.

The basic syntax for COUNTIF is: =COUNTIF(range, criterion)

Just like SUMIF, you set the range and then the condition that needs to be met. For example, if you wanted to count the number of times Food came up in the Expenses column, you could use a COUNTIF function like this:

COUNTIFS has the same basic syntax as SUMIFS: =COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

The criteria_range and criterion are in the same order, and you can add more conditions to the end of the function. So, if you wanted to find the number of times Coffee appeared in the Expenses column on 12/15/2020, you could use COUNTIFS to apply those conditions, like this:

This formula follows the basic syntax to create conditions for “Coffee” and the specific date. Now we can find every instance where both of these conditions are true.

SUMIFS and COUNTIFS are just two examples of functions with multiple conditions. They help demonstrate how multiple conditions can be built into the basic syntax of a function. But, there are other functions with multiple conditions that you can use in your data analysis. There are a lot of resources available online to help you get started with these other functions:

• How to use the Excel IFS function: This resource includes an explanation and example of the IFS function in Excel. This is a great reference if you are interested in learning more about IFS. The example is a useful way to understand this function and how it can be used.
• VLOOKUP in Excel with multiple criteria: Similar to the previous resource, this resource goes into more detail about how to use VLOOKUP with multiple criteria. Being able to apply VLOOKUP with multiple criteria will be a useful skill, so check out this resource for more guidance on how you can start using it on your own spreadsheet data.
• INDEX and MATCH in Excel with multiple criteria: This resource explains how to use the INDEX and MATCH functions with multiple criteria. It also includes an example which helps demonstrate how these functions work with multiple criteria and actual data.
• Using IF with AND, OR, and NOT functions in Excel: This resource combines IF with AND, OR, and NOT functions to create more complex functions. By combining these functions, you can perform your tasks more efficiently and cover more criteria at once.

In previous activities, you used basic spreadsheet functions such as COUNT, SUM, AVERAGE, and MAX. In this activity, you will work with the conditional versions of these functions: COUNTIF, SUMIF, AVERAGEIF, and MAXIFS.

Conditional functions are functions that perform a specific task, but only on cells that satisfy some defined criteria. They are usually identified with an IF suffix adjoined to the desired operation. They are frequently used when constructing more complex queries that cannot be accomplished using more basic functions.

By the time you complete this activity, you will be able to use conditional functions and understand when and why they are appropriate. This will enable you to do more complex analysis with spreadsheets as you continue to develop your data analyst’s skill set.

What you will need

To get started, first access the Working with Conditions spreadsheet.

Click the link to the spreadsheet to create a copy. If you don’t have a Google account, you may download the spreadsheet directly from the attachments below. Make sure to select “Use Template” on the downloadable item.

Link to spreadsheet: Working with Conditions

OR

Download spreadsheet: Working with Conditions

This data set has seven columns and 20 rows (not including the headers). The contents are several metrics pertaining to a fictitious team of salespeople.

First, open the Working with Conditions spreadsheet.

Suppose you want to calculate the number of salespeople that the company has in New York state. The COUNTIF function allows you to do this easily. The syntax for COUNTIF is =COUNTIF(range, criteria).

The range is the array (or collection) of cells that you are checking and the criteria is what you are checking for. All cells in the array that match the provided criteria will be counted and this number returned as the value of the function.

To use this function to count the number of salespeople working from “NY,” click on an open cell. In the function bar, type =COUNTIF(B2:B21, "NY").

Notice you've entered the range as the array of cells from B2 to B21. This is all from column B with the exception of the column header. The function checks all cells in this array against the value “NY” (entered in quotes) which is the criteria. Every cell in this array with a value of “NY” will be counted, and the result is returned in the cell. It is 6 in this case.

Press Enter (Windows) or Return (Mac). The result should display like this:

As an alternative to entering the criteria "NY" into the COUNTIF function, you can achieve the same result by entering a cell address as the criteria. The function will then use the value of the cited cell as the criteria. For example, the cell J10 has the value "NY." If you enter this in the function bar the COUNTIF function will seek out the value in cell J10 and use it as the criteria. This gives the same result as before:

The SUMIF function is used to create a sum of the values of cells that meet a specific criteria. It supports the logical operators (>, <, <>, =). The syntax for this function is =SUMIF(range, criteria, [sum_range] ).

The input range is the array of cells that you check against the value of criteria. The sum_range is the array of values that you will sum up if the criteria is met. In this syntax above, the square brackets around sum_range indicate that this input is optional. However, you do not add square brackets when writing the function. If the argument sum_range is absent, then the SUMIF will sum the values in range by default.

As an example of this function, suppose that you want to create a sum of all sales more than $500.00. This can be executed as =SUMIF(D2:D21, ">500").

The result is:

Because you didn't include the sum_range input, all the values in the cells D2 to D21 that match the criteria were summed by default. To sum only the sales from New York, but not restrict to those greater than $500, type the following function: =SUMIF(B2:B21, "NY", D2:D21).

Notice that in the SUMIF, the first input, B2:B21, is the range of cells that are checked for the criteria "NY" and the summing is done across the sum_range of cells D2:D21 that have the state meeting the criteria "NY." This is different than in the first case. In that case, the array that you check is the same array that you sum across.

Use the AVERAGEIF function

Just like the previous two functions, the AVERAGEIF function will average the values in an array based on a given criteria. The syntax is =AVERAGEIF(range, criteria, [sum_range]).

The inputs to this function, range, criteria, and sum_range, work in exactly the same manner as in the SUMIF function. Again, the sum_range is optional.

Now, find the average sales per salesperson in New York. Type the following function: =AVERAGEIF(B2:B21, "NY", D2:D21).

This yields 902.83333 as the result.

The MAXIFS function is slightly different from the other three functions. The easiest way to observe the difference is to examine the syntax: =MAXIFS(max_range, range1, criteria1, [range2], [criteria2], ...).

• Note for Microsoft Excel users: MAXIFS can only be used with an Office 365 subscription on Excel 2016 or newer. If you cannot use a version of Excel that allows the function MAXIFS, please use Google Sheets for this part of the activity.

The first argument, max_range, is the array over which you are finding the maximum. The second argument (range1) is the array you are checking. The third argument (criteria1) is the value that you are checking for. The inputs in the square brackets are for optional additional constraints.

Use this function to find the maximum sales from any salesperson in New York. Type the following: =MAXIFS(D2:D21, B2:B21, "NY").

The resulting calculation is 1666.61.

Remember, the order in which you enter the inputs matters. Try reversing the position of the arrays from the first example and type =MAXIFS(B2:B21, D2:D21, "NY").

The result is 0.

This is because you are asking the function to find the maximum of the array B2:B21 where the sales equal "NY". This is impossible because the values in the array D2:D21 (the sales array) are numerical. Therefore, none of them equals "NY," which is a string. The function returns 0 when nothing in the range meets the criteria.

The MAXIFS function can input more than one constraint. This is where the optional range2 and constraint2 come into play. Additional constraints follow the logic that every constraint must be satisfied for a cell in the max_range to be considered.

For example, to find the maximum sales in New York where the Max Item Cost is below $400, type the following into the function bar: =MAXIFS(D2:D21, B2:B21, "NY", E2:E21, "<400").

The first three inputs are the same as above, but now you've added the additional constraint that Max Item Value must be less than $400. The array E2:E21 is the Max Item array and its cells are checked against the criteria <400. The function returns the following, which is the maximum sales of any New York salesperson who did not sell any single item over (or equal to) $400.

Each of the previous functions—COUNTIF, SUMIF, and AVERAGEIF—have equivalents that work similarly to MAXIFS. These include COUNTIFS, SUMIFS, and AVERAGEIFS. The syntax and functionality of these functions, apart from the specific calculation, are identical to MAXIFS. For example, the SUMIFS function will give the sum for single and multiple constraints just like MAXIFS function does for the maximum. It also has the same syntax as MAXIFS.

In this activity, you tested the query =COUNTIF(B2:B21, "NY"), which returned the value 6. Suppose you want to determine how many of those 6 salespeople have only 1 client. You run the query =COUNTIFS(B2:B21, "NY", C2:C21, "1") to find this information. What value does this return?

A. 4

B. 1

C. 3

D. 5

The value will return is 4. Explain: The query =COUNTIFS(B2:B21, "NY", C2:C21, "1") returns the value 4, since there are 4 salespeople in New York that have only one client. To find this information, you used the COUNTIFS function with additional constraints. Going forward, you can use other conditional functions to find specific information from your data, which will help you carry out more complex analyses.

In this activity, you used functions with multiple conditions to answer questions about your data. In the text box below, write 2-3 sentences (40-60 words) in response to each of the following questions:

• How can you use conditional statements with functions to create complex queries?
• When is it appropriate to use a function with multiple constraints, such as SUMIFS, rather than a function with a single constraint, such as SUMIF?
• What are some other situations where you might prefer to use a conditional function instead of a regular one?

Explain: Congratulations on completing this hands-on activity! A good response would include how conditional statements can be used to create complex queries and functions to perform tasks on an array that meets one or more criteria.

The suffix -IF is a common syntax addition that will allow you to implement more complex queries and functions. Many of the basic functions allow for an -IF and most of those also allow -IFS as well. By using these more complex functions, you can expand your spreadsheet skillset and analyze data more effectively in programs like Google Sheets and Microsoft Excel.

What is the correct spreadsheet formula for multiplying 50 and 233?

A. 50*233

B. 50x233

C. =50*233

D. =50x233

The correct spreadsheet formula is C. =50233. Explain: =50233 is the correct formula for multiplying 50 and 233. Formulas begin with an equal sign (=). This is followed by the values to be computed. An asterisk (*) is the multiplication operator in spreadsheets.

	A	B
1	Expense	Amount
2	Rent	$680.00
3	Healthcare	$101.00
4	Groceries	$185.00
5	Clothing	$41.00
6	Transportation	$22.00
7	Mobile phone	$48.00
8	Dining out	$79.00
9	Car insurance	$65.00
10	Dog walker	$40.00
11	Gym membership	$19.00
12	Manicure	$23.00

You are trying to determine what percentage of your monthly income is spent on big-ticket items, such as rent and groceries. To add together only the values from Column B that cost more than $150, what is the correct syntax?

A. =SUMIF(B2:B12,<150)

B. =SUMIF(B2:B12,"<150")

C. =SUMIF(B2:B12,">150")

D. =SUMIF(B2:B12,>150)

The correct answer is C. =SUMIF(B2:B12,">150"). Explain: To add together only the values from Column B that cost more than $150, the correct syntax is =SUMIF(B2:B12,">150"). B2:B12 is the range, and more than 150 (>150) is the criteria.

You may click the link to create a copy of the dataset: Cosmetics Inc

Or, if you don’t have a Google account, download the template directly from the attachment below:

Cosmetics Inc. - Sheet1

Which of the following is an example of an array in this spreadsheet?

A. All cells with values greater than 100

B. All cells with number values

C. The values in cells B2 through B31

D. Cells D7 and D14

The correct answer is C. The values in cells B2 through B31. Explain: The values in cells B2 through B31 together are an example of an array. An array is a collection of values in spreadsheet cells.