Excel includes a number of very useful tools you can use to ensure your formulas use the proper values. One of those tools gives you the ability to determine which cells are used in which worksheet formulas.
- [Instructor] In the previous movie, I demonstrated how to use the IF function to create conditional calculations. In this movie, I'll describe how to create other conditional calculations. My sample file is MoreConds, M-O-R-E-C-O-N-D-S dot XLSX, which you can find in the Chapter Five folder of the exercise files collection. The first is SUMIF. If you want to find the total of a certain number of values that meet a given criteria, then you use the SUMIF function.
So in cell F2, we'll type an equal sign and the function SUMIF. I'm only doing one criterion so I'll just use the IF, left parenthesis. The range is the criteria range. We'll do the sum later, although they can be the same. So let's say that I want to look for sales of batteries. So I will select cells from category so that'll be B2 through B9, then a comma. The criteria, we need to put in quotes, and that will be equal batteries followed by double quote, comma, and then the sun range is C2 though C9.
Right parentheses and Enter. And we get the total of battery sales of 203,590, and 76,635 plus 126,955, yup, that sounds about right. You can also find the average. So let's say that we want to find the average for 2018. In cell F3, I'll type equal averageif. Then our parenthesis, the range is A2 through A9. Again, I'm looking for 2018, comma, the criteria needs to be in quotes so double quote equal 2018 double quote comma and the average range is my revenue C2 through C9, rear parenthesis Enter and there's the average.
I can also count and in this case, if I type equal and then countif, followed by left parenthesis, I only need to give the range and the criteria. That's because I'm not adding anything. I'm just counting. So if I want to see how many values I have for lightbulbs in the category column, I can select cells B2 through B9, comma, and then my criterion will be in double quotes equal and let's say they say light bulbs, we'll do light bulbs.
That's in double quotes, right parenthesis, Enter, and I get two, and it's formatted as dollar sign but don't worry about that for now. SUMIFS allows you to do multiple criteria so in cell F5, I'll type equal and then SUMIFS. That's with the s on the end, left parenthesis. The syntax for these multiple condition or multiple criteria functions changes. I have the sum range first. That is C2 through C9 comma.
Now, I can define my criteria range and criteria pairs. The first criteria range will be sales for the year of 2017 so I'll say A2 through A9, that's the criteria range then a comma and then we'll make the year that I'm interested in let's say 2018. In double quotes equal 2018, close the double quotes. Now I can create my second criteria.
And let's say that those will be the revenues. So I will say that the revenues and again those are C2 through C9, but I need to select them again, comma, double quotes for my criteria. Let's say less than or equal to 100,000. Followed by right parenthesis and Enter. Found a typo. Oh, I forgot the close the double quote, okay, and it's offering to create a correction. I'll say no, don't accept it.
Click OK, yes I know there's an error. And I need to close the double quotes and Return and there we go. I get the sum from 2018 of all values that were less than 100,000 and in this case, there's only one, 38,327. We can do the same thing for AVERAGEIFS. So I type equal averageifs. I'll go a little more quickly because it's the same type of thing. The revenue is C2 through C9 comma.
The criteria range, let's say that the category is the year. Comma, double quotes equal 2017, double quotes comma. Criteria range two will be the revenue, C2 through C9 then the comma, and we'll use the same one that we did before for SUMIFS. So double quote, less than or equal to 100,000, close the double quotes this time, right parenthesis and return and I get the average of 87,318.
If I want to count the number of values from 2017 that are less than or equal to 100,000, I can use COUNTIFS. So in F8, I'll type an equal sign, COUNTIFS for multiple criteria. Criteria range will be the first one, A2 through A9, that's for the year, comma, double quote, equal to 2017, close the double quotes then a comma, criteria range two will be C2 through C9 then a comma, double quote and less than or equal to 100,000.
Close the double quote. Right parenthesis and Return and I get three. Creating conditional formulas might seem confusing at first, but after you create a few, they will become second nature.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks