This formula has different types of references, so it can be safely copied down to the Cost total in row 12, and across to column F. To finish the worksheet, the formula is copied across to column F, and copied down to row 12, where it calculates the total cost. To make the formula more flexible, so it can be used in the Cost row too, we’ll use a reference to the heading cell - $A11 - instead of hard-coding the “Hours” text.Ĭopy the Formula to Remaining Total Cells To total all the Hours rows in week 1, we could use this formula: The wildcard character – * – represents any characters, so the criterion “*Hours” would match any heading that ends with “Hours”. In the rows with costs, the heading ends with “Cost”.In the rows with hours, the heading ends with “Hours”.However, the headings are consistent, and the endings match the total headings in A11 (Hours) or A12 (Cost). The row headings won’t exactly match the “Hours” criteria, because the headings start with the employee name, such as “Emp01 Hours”. Note: If you have multiple criteria to check, use the SUMIFS function in Excel 2007 and later versions. With a SUMIF formula in cell C11, we can sum the hours in column C.The total headings in A11 (Hours) or A12 (Cost) will be used to create the criteria.Row headings are in column A, so that is the range the SUMIF formula will check.=SUMIF( range to check, criteria, range to sum)
It sums the numbers in rows that meet a specific criterion. That solution might work in a short list, but it’s an accident waiting to happen if you try to total a long list with this technique.Īnd even in a short list, you’ll end up with errors if an employee’s data is deleted from the list.Ī better solution in this case is to use the SUMIF function. One way that people do this is to use the SUM function, and carefully select all the rows with hours. Instead of just employee hours, the worksheet also calculates the weekly cost per employee. You’ve probably seen worksheets like the one below, in which different kinds of values are mixed together. In the example shown below, the SUM function calculates the total of hours worked each week. It takes a little extra though, to sum Excel numbers in one column, based on text in a different columns. The interesting thing I learned is that, even if the choices were in reverse order, for example summing from Prod8 to Prod4, the process still works!Īnd that's how you can use the XLOOKUP function to SUM a dynamic range of values in Excel.Most of the time, if you’re summing numbers in Excel, a simple SUM formula will do the job. So now I can change any of the products and it will automatically SUM the values between those two choices! I just wanted to show this process so that you had another option when building large formulas, to create them in smaller chunks, then just paste them into the larger, final formula.) You can just create it as you might any similar formula. (Obviously, you don't have to use the Clipboard process to create a formula like this. Now that I've copied them, I can easily build my SUM formula without having to retype them:Īll I had to do is start my SUM formula with =SUM(, then click on the first XLOOKUP formula I copied, type in a colon, then click on the second XLOOKUP formula, and close my SUM function with a parenthesis. Notice that I copied the formulas (using the keyboard shortcut Ctrl + C) without the equal signs. So I don't have to read type these two formulas that I just created, I am going to use the Clipboard to make it easier to build this formula: Now what I want to do is SUM up the values between those. I am first going to create the formulas to look up the item for the From: – Prod3 and To: – Prod8. As I mentioned this is only available in Office 365. I have a list of 20 products and their values and I want to be able to choose two of the different products and get the SUM of the values between those two products:
When you get a preview, look for Download in the upper right hand corner.
You can download the file here and follow along. Excel, and in the process of testing it, learned a really interesting feature. This is a trick I learned from Bill Jelen, Mr.
In this tutorial we are going to learn how to use the XLOOKUP function to SUM a dynamic range of values in Excel.