[MUSIC] Some summary functions exist within Excel to aggregate data on a conditional basis. Ones important such functions is COUNTIFS. COUNTIFS allows you to count the number of occurrences of a specific condition across a range of data. Let's use COUNTIFS to answer the questions in problem 1a. Problem 1a asks us to find the number of sales of each of the products listed in column bravo. COUNTIFS takes as parameters the range of data you wish to count from, and the criteria to satisfy when counting values in that range. Let's point COUNTIFS from our product column of the dataset using the value in cell bravo 12 and set it to work. You'll notice the clarity that structured references provide our formulas, as Excel has intelligently substituted the table name and column name along with some special syntax for the typical cell references we see. We could type these values into the COUNTIFS formula directly using tab completion and going forward, we may do this out of convenience. You'll also notice that because we have formatted the problem table as an Excel table, the formula automatically fills down when we type it into the first cell of a column. Let's validate our answers manually to confirm that we've calculated what we intended to. This is a good practice for your own spreadsheet analysis. We see that COUNTIFS counted 11 sales of the pure soft detergent 100 milliliter product. If we pop back over to the dataset, we can filter on this value to validate our answer. We see that indeed there were 11 sales of the 100 milliliter detergent product as verified by the count value in the bottom right side of the status bar of Excel when we highlight the filtered cells. The next part of our problem asks us to count the number of sales for which revenue meets certain criteria. COUNTIFS can be used to quickly calculate such summaries. We can pass COUNTIFS building in tests to apply against values in the range we are counting from. Let's see how this works. If we want to count the sales which resulted in more than $1,000 in revenue, we first pass COUNTIFS the range of values to count from. In this case the revenue column of our data table. Next, we posit the criteria to evaluate. In this case, a string of text, the syntax of which should feel very familiar. It's greater than 1,000 in quotes, and then we set the formula to work. Let's validate our answer against the dataset by filtering down revenue values for those greater than 1000. Excel provides a simple way to filter by number. Let's select the number filters option from the filter drop down on the revenue column of the dataset. Then we'll select greater than, and type 1,000 into the dialog box. We see that, indeed, there were 27 sales which produced revenues of $1,000 or more. We can copy this formula to the next cell and make one small change, and answer the question of how many sales resulted in revenues of less than $1,000. Let's quickly change the greater than symbol to a less than symbol and set COUNTIFS to work. We can validate our answer in a similar fashion as previously and confirm that there 80 sales less than $1000. We'll select the less than option from the number filter's dropdown menu this time. We can copy this formula again to the next cell and make another small change to answer the question of how many cells resulted in revenues exactly equal to $1000. Let's quickly change the less than symbol, and set COUNTIFS to work. We can validate our answer in a similar fashion as previously. And confirm that there were no sales resulting in revenues equal to $1000. We'll select the equals option from the number filter's drop-down menu this time. We can copy this formula to the next cell and make another small change, to answer the question of how many sales resulted in revenues not equal to $1000. Let's quickly change the less than symbol, and set COUNTIFS to work. We can validate our answer in a similar fashion as previously and confirm that there were 107 sales resulting in revenues not equal to $1,000. We'll select the does not equal option from the number filters drop down menu this time. Now it's your turn to demonstrate your understanding of COUNTIFS by completing problem 1b. [MUSIC]