Transcript:

Hi everyone. This lesson will be about semi-logical functions. Semi-logical functions evaluate a cell to see if it meets a certain criteria, and then perform an action. We’ll be looking at six semi-logical functions in this lesson. We will do a COUNTIF, a SUMIF, and an AVERAGEIF. COUNTIF and SUMIF are available in both Office 2003 and Office 2007. AVERAGEIF is only available in Office 2007. Three more functions we’ll look at, that are only available in Office 2007, are COUNTIFS, SUMIFS, and AVERAGEIFS. These three allow you to do a count, sum, or average, based on more than one criteria. Let’s look at three examples, before we start with our data.

The first semi-logical function we will do is a COUNTIF. A COUNTIF will count a cell if it meets a criteria. In this case, we’ll look at a salesperson, and the amount that they’ve sold. We’re going to count if the salesperson is Jane. Each time Jane appears in the Salesperson column, one will be added, so the result of our COUNTIF in this example will be four.

In our next example, we’ll do a SUMIF. In this case we’re going to sum the amount in the Amount column, if the salesperson is Jane. So each time Jane appears in the Salesperson column, her sales amount will be added. So our result for the SUMIF will be 3317.
For our last example before we look at Excel data, we’ll look at what an AVERAGEIF does. In this AVERAGEIF, we’re going to average the amount in the Amount column if the salesperson is Jane. Each time Jane appears in the Salesperson column her amount will be set aside, added, divided by the count, and then we’ll have an average.

Now let’s practice in Excel.

First, let’s look at our data. Here we have a company name, an industry, an exchange code, their sales figures, the cost of sales, gross margin, and gross margin percentage. At the top we have questions. We’re going to evaluate this data to see how many securities brokerages we have, our total sales for securities brokerages, total sales for companies with a gross margin percentage greater than 75%, average sales for the machinery industry, how many NYS banks we have, total sales for NYS banks, and average sales for NYS banks.

So if we wanted to count how many securities brokerages there are, we could look in the Industry column, scroll down, and count how many times we see “Securities Brokerage”. But that seems like a real waste of time. Instead we’ll use the COUNTIF function.
So, we’ll start by, in cell C3, typing =COUNTIF and opening parentheses. Now, notice in the screen tip below the cell, the first thing it asks for, the first argument, is “range”. Range is bold right now. That shows us that that’s the argument we’re on in the formula. Our range will be the place we look for our criteria. So our range is going to be the Industry column.

In this tutorial, I’m going to use keyboard shortcuts to do my functions. You are welcome to use the mouse if you want to, but I’ll teach you how to use keyboard shortcuts to do functions.

I’ll use the down arrow key on my keyboard to arrow down to the first cell in my range. Then, to select the entire range, I’m going to use a keyboard shortcut. I’m going to use Ctrl + Shift + Down Arrow. So I’m going to hold down Ctrl, then Shift, and press the down arrow on my keyboard. And that has selected my entire continuous range.

I’m going to type a comma to move on to the next argument in our function, which is criteria. Our criteria is “securities brokerage”, because we’re looking for securities brokerages. Your criteria has to be in quotes. So I’m going to open my quotes and type securities brokerage, and close my quotes. Your criteria is not case sensitive, but it does have to be spelled correctly. Now, to finish my formula, I’m going to close my parentheses and hit the Enter key on my keyboard. If I scroll up a bit, you can see that we have eight securities brokerages in our data.

So, that’s a COUNTIF.

Now let’s say we want to see what the total sales for all the securities brokerages are. For this we’ll use a SUMIF formula. The SUMIF formula will evaluate the Industry column, see if a company is a securities brokerage, and will take the sales, put that aside, and total the sales for each securities brokerage. So, to do our SUMIF, start with the equals sign, and type SUMIF, and open parentheses. Our range is the place we’re looking for our criteria. Because our criteria is securities brokerage, we’ll select the Industry column as our range again.

I’m using the down arrow key to arrow to the first cell in the Industry column. I’m going to use the Crtl + Shift + Down Arrow keyboard shortcut to select the Industry column. Now, to move on to the next part of my formula, I’m going to type a comma, and you can see that now, in the screen tip, “criteria” is bold. That shows me I’m on the criteria portion of the formula. Just like in the last formula, our criteria is securities brokerage, so in quotes, I’m going to type securities brokerage, and close my quotes.

Now I’m going to type a comma to move on the the last portion of my formula. This is the “sum range”, and the sum range is asking us what we want to sum if the criteria is met. We want to sum the sales if the criteria is met, so we’re going to select the Sales column. Now even though it looks like we’re at the bottom of our worksheet, I’m actually going to use the down arrow key to arrow to the Sales column. That’s because even though we’re looking toward the bottom of our worksheet, we’re actually up in cell C5. I’m going to press the down arrow on my keyboard, and you can see that it moved me back up to the top. I’m going to arrow down, and then over to the right to select my Sales column. I’m in the first cell of the Sales column and I’m going to press Ctrl + Shift + Down Arrow to select it. Now I’ll close my parentheses and press the Enter key and arrow up to see my total sales.

That’s one SUMIF, using text as my criteria. Let’s look at another SUMIF where we’re using numbers for our criteria. We want to know the total sales for companies that have a gross margin percentage greater than 75%. For this formula, again, we’re doing a SUMIF, so I’ll type =SUMIF. Notice that as I type Excel shows me formulas below that start with those letters. If you want to select from the formulas shown below, you use your arrow keys to select the one that you want and then press the Tab key on your keyboard, and that will finish entering your formula and open your parentheses for you. So I’ve got equals SUMIF, and I’ve opened my parentheses. My range is where I’m looking for my criteria. In this case my criteria is a gross margin percentage greater than 75%. So my range will be my Gross Margin % column. I’m going to use the down arrow key to arrow down, and then the right arrow key to arrow to the right until I get to the first cell in the Gross Margin % column. Now I’m going to press Ctrl + Shift + Down Arrow on my keyboard, and I’ve selected my range. I’ll type a comma, and then I’ll enter my criteria. My criteria is greater than 75%. I actually have to put that in quotes as well, just like we did with securities brokerage when that was our criteria. So, I open my quotes and I’m going to type the greater than (>) symbol and then type 75%. If you want to, you could type the percentage as a decimal. You could type .75 and it would give you the same results.

I’m going to type a comma to move on to the next part of my formula, which is the sum range. Just like in the last formula, our sum range is the Sales column because we’re looking for the total sales. Even though we’re looking at the bottom of our worksheet, we’ll use the down arrow key to move down, and then we use the right arrow key to move to the right to select the Sales column. Use Ctrl + Shift + Down Arrow to select the entire column. I’ll close my parentheses and hit the Enter key. And now I have my total sales for companies with a gross margin percentage greater than 75%.

Now we’re coming to a formula that is not available in Excel 2003. AVERAGEIF is new to Excel 2007.

We’d like to look at the average sales for companies whose industry is machinery. We’ll start with an equals sign and type AVERAGEIF, and I will use the down arrow key to arrow down to AVERAGEIF in the box below, and hit the Tab key on the keyboard to select it. My range is where I’m looking for my criteria. That’s going to be Industry. I’m using the down arrow key to arrow down to Industry. I’ll press Ctrl + Shift + Down Arrow to select the Industry column. Then I’ll type a comma to move on the the next portion of my formula. We can see from the screen tip that our next portion is our criteria. The criteria is that it needs to be machinery. In quotes we’ll type machinery. Close the quotes, and then type a comma. For average range, we’re looking to average the sales, so we’ll select the Sales column. I’m going to use the Down Arrow key. It takes me back to the top of my worksheet, then I’ll arrow over to the Sales column. From the first cell in the Sales column, I’ll press Ctrl + Shift + Down Arrow to select my range. I’ll close my parentheses, and hit the Enter key. Now if I arrow up a bit, I can see what the average sales for Machinery are.

Now let’s look at some semi-logical functions that allow us to have more than one criteria. These three functions are all new to Office 2007.

The first is COUNTIFS. We’ll start it by putting in =COUNTIFS. I’m going to arrow down to COUNTIFS box and hit the Tab key. We’re looking to see how many banks we have that have an NYS exchange code. To do this, we will arrow down to the Industry column, where one of our criteria is, and we’ll use Ctrl + Shift + Down Arrow to select it. We’ll type a comma, and here we put in our first criteria. Our first criteria is that it has to be a bank. We’ll put bank in quotes, and then type a comma to move on to the next criteria. Our other criteria is that it be an NYS exchange code, so I’m going to arrow down, and then arrow one to the right until I get to the Exchange Code column. Ctrl + Shift + Down Arrow to select the column. I’ll type a comma. Now I’ll put in my second criteria, which is that the cell contain NYS. Again, that needs to be in quotes. We’ll close the parentheses and hit Enter. So we have sixteen banks with an NYS exchange code.

Now let’s look at the total sales for NYS banks. So, since we’re looking for the total sales, we’re going to do a SUMIFS. SUMIFS allows us to sum based on more than one criteria. So we’ll start with =SUMIFS. Notice, this time, instead of putting our criteria first, it actually asks for the sum range first. We’re going to sum the sales, so we’ll arrow down to our Sales column. Select the entire Sales column by using Ctrl + Shift + Down Arrow, then type a comma. Our criteria range is where we’re looking for our criteria. The first criteria is that it be a bank. I’ll use the down arrow key to move to the Industry column. Ctrl + Shift + Down Arrow to select the Industry column , where we’re looking for the criteria. I’ll type a comma to move on to the next part of the formula, which is where I put in my first criteria. My first criteria is that it be a bank, so I’ll open quotes, type bank, close quotes, and type a comma. Now the formula is asking for criteria range 2, the range for our second criteria. I’m going to use the down arrow key to arrow down, and then over one to the Exchange Code column, because our second criteria is that the exchange code be NYS. I’ll use Ctrl + Shift + Down Arrow to select the column, and type a comma to move on to my criteria. My criteria is that the Exchange Code column contain NYS, so in quotes, I’ll type NYS. It doesn’t actually have to be capitalized; it’s not case sensitive. I’ll close my parentheses now. I’ve finished my formula. Hit the Enter key and arrow up. This is the total sales for NYS banks.

With our AVERAGEIFS formula we’ll find what the average sales for NYS banks are. I’ll start with =AVERAGEIFS, I’ll select it from the menu and and hit the Tab key. Again, instead of having the average range at the end of our formula, our average range is the first thing that we enter before our criteria. We’re averaging the sales for NYS banks, so we’ll select the Sales column. Arrow down, and then to the right, until you get to the first cell in the Sales column. Press Ctrl + Shift + Down Arrow on your keyboard to select that as your average range. Then type a comma. Our criteria range 1 will be Industry, because we want to make sure that Industry is bank. Use the down arrow key to arrow to the first cell in the Industry column. Ctrl + Shift + Down Arrow to select the Industry column, then type a comma. Open quotes, and we’ll enter our criteria for the Industry column, and that is Bank. Be sure to close your quotes, then type a comma. Our second criteria is that the exchange code by NYS, so our criteria range will be the Exchange Code column. Arrow down to the first cell in the Exchange Code column, then use Ctrl + Shift + Down Arrow on your keyboard to select it. Now that we’ve selected the criteria range we’ll type a comma to move on to the next part of our formula, which is criteria 2. Criteria 2 is that it be NYS. So, open quotes, type NYS, close the quotes, then close the parentheses on the formula. Hit the Enter key. We’ll arrow up to see the results. This is the average sales for NYS banks.

This was an introduction to semi-logical functions. Thank you for watching.