Functions are now much easier to work with in Excel 2016. Firstly, the function can be created quite simply by starting to type into a relevant cell. In this case, I'll be adding the mileage figures. As soon as I start typing this function, you will notice that it auto-completes as I type it, or I can physically select the function. Double click to complete it. And then add the relevant criteria to it before pressing enter to complete the function itself. You can also create a function by selecting it from the formulas ribbon tab. If I click formulas, I can see that there are a series of categories. And as I click a category, I can see that there are function options showing. In the case of what I'm looking for, which is an average function, I'm going to go to AutoSum and in the category there, I will pick Average. And as you can see, there's my function. And again, I will press enter to complete. 

Excel 2016 also comes with a number of new functions. These include functions: COUNTIFS, SUMIFS, and AVERAGEIFS. These allow you to use multiple criteria to display results. In this particular example, what I want to do is, in row three, display all the IT people who are based in Sheffield and count how many people there are. To do this, I'll perform a COUNTIF function. However, before I do this, to make it very easy for me to find the data, I'm going to convert all of the columns into what are called name ranges. 

To do this, I will click into a single cell inside my table, press CTRL+A to select my entire table, go to my formulas tab, and in the Defined Names group, I select the command Create from Selection, click this. Make sure that you are only picking information from the top row before clicking okay. What this will do is every single column will now be a named range. I can see this by clicking on the name button on the left-hand side and selecting for example, location, to realise that all of the data beneath the location heading is now a name range of cells and can be easily found by Excel. To create my function, I'm going to click onto the relevant cell to start typing my function. So this is going to be a COUNTIFS function. I'll press equals and start typing count. And from the list, I will go and select COUNTIFS. 

This time I'm going to select my tab key to complete the first part of the function. It now gives me the arguments of the function. The first thing I'm interested in is my first criteria range, which in this case, I'm going to start typing dep and it will find the name range of department from my table. I'm going to press tab and you will see that this is highlighted. Press comma and it now wants a criteria. So in this case, I'm now looking for the IT team, which I will now type in speech marks before pressing comma again and looking for a second criteria. This will come from my location column. Again, I'm typing loc. There's my location name. I'll press tab again and add another comma again. And it will now go to a second criteria. And in this case, I'm looking for Sheffield, which I can place in speech marks like that. That's the completion of my formula and I just close the brackets, press enter, and there I find that there are eight rows of data that match both my team and Sheffield. 

The next formula that I'm going to add underneath is going to be a total for exactly the same criteria. In this case, I will use a SUMIFS function. I'm going to press equals, find the function that I'm after, which is called SUMIFS again, pressing tab to complete. Initially, I want to know what's the information that I want to add up, and this is going to mean my salary column. I'll then say, right, I want to add up my salary where my first criteria is department, IT as before. My second criteria of location matches Sheffield. I'll complete my function and find that I have 211,410 pounds in my total salaries for those eight people.