Uma has been asked to help work out the total sales brought in by each account manager. The COUNTIFS function will allow her to count the number of sales, but not add up their total value. For this operation, we need the SUMIFS function instead. But rather than counting the number of times it find cells that meet certain criteria in a range, it adds up the corresponding values in those cells instead. Let's try it out. We've already clicked on the sales dash worksheet from the previous video, so now click on cell B21. We want to sum the total sales for Connor Betts. So start by typing =SUM, down arrow twice to highlight SUMIFS. Make sure you choose SUMIFS plural, instead of SUMIF singular. Older versions of Excel only have SUMIF, which is okay for today, but it is less powerful. Tab to choose the function. Observe the prompts. The first thing it is asking for, is our sum range. These are the values we want to add. We have already named our ranges in the previous video, so just type tot and tab to select Total. Comma to specify the next argument, which is the criteria range. Type ac and tab to select Account_Manager, which is our criteria range. Comma to supply the next argument, which is finally the criteria. Click on Connor Betts in cell A21, and Ctrl+Enter to commit the function but still stay in the cell. Connor Betts sales in total were $135,493.75. Great, double click on the fill handle to do this for each of the account managers. As you can see, the summarized data is now available for us, and it's actually in the chart that has already been embedded into this dashboard. SUMIFS is ideal for summarizing such data. So we have seen how we can summarize data using a single criteria. But Uma has also been asked to get total sales by each manager, for each year. That means we have two criteria. You can have up to 127 criteria, but I hope you never have to create that function. Let's start with 2 criteria. Clicking cell C21 and type =SUM, down arrow, down arrow, tab to select SUMIFS. Type Tot, tap to select Total as the sum range. Comma to specify the next argument which is account manager, ac tab, comma followed by the criteria, which is the account manager's name, Connor Betts. Comma to specify the second criteria. Note that the program offers the option of a second criteria range. So let's type ord and double click Order_Year to select that. Comma, to set next argument which is the criteria, the year which is in cell C21, Ctrl+Enter to commit the function but still stay in the cell. Well done. We're not quite there yet, because we want to drag the formula down, as well as drag the formula across, and we're getting zeros everywhere, why? The problem is relative cell references. When we drag across, we need column A to be fixed, but relative when we drag down. And vice versa for row 20. To do this, we have to use a mixed reference. Click on cell C21, to get the formula in the formula bar. Go across to the formula bar, and click anywhere in A21. While this cursor is on A21, press F4 and see what happens. It's changed to an absolute cell reference. Press F4 again, and press F4 a third time. The $ is now only in front of A, which means the column is locked. But not in front of the 21, which means that the row is relative. Click on C20, press F4 once to get an absolute cell reference. Press F4 again, the $ is in front of 20, which means row 20 is locked. But there's no $ in front of the C, which means the column is relative. Ctrl+Enter to commit our changes. Drag the formula down to get that for all of their account managers. And drag that across to get that for each year. Great, we actually have values in all of our cells. Mixed cell references take a bit of getting used to. But if you get confused, just try one way, and if it doesn't work, do it the other way. Remember the $ locks whatever comes just after it. And remember practice makes permanent. Review mixed cell references in the toolbox this week, to make sure you understand how they work. We now know how to use both COUNTIFS and SUMIFS, to summarize data that meet one or more criteria. If you're feeling adventurous, try out AVERAGEIFS.