Single-cell Formula to Filter Specific Columns and Calculate Running Total in Excel

Hello and welcome to this Microsoft Excel video. My name is Abiyola Davy. In this amazing video, I'm going to show you how to use a single cell formula to filter for the total sales that is greater than or equal to 10,000 and total sales that is less than or equal to 100,000

returning four specific columns from the original seven columns. And internally, we're going to use the amazing new scan function to calculate the running total or cumulative total

Let's get started. We're going to focus on this sample data set, which is officially stored in an Excel table. In the table design contextual report tab, Rt is given as the meaningful table name

So we're going to use that in our formula. I'm going to alter the formula in cell I8. I'm going to continue in the formula bar and type in equals filter

I'm going to return these records in the Rt table. I'm going to put in a comma. For the second argument of the filter function, we want to actually return all the records where the total sales is greater than or equal to 10,000 and less than or equal to 100,000

So I'm going to open the bracket for the first criteria. I want to check this total column and use the comparative operators, greater than or equal to 10,000 without separator and then close the first criteria

Now since this is an AND logical operation, I'm going to use the asterisk sign and open the bracket for the second criteria. So I'm going to actually see the same total that is less than or equal to 100,000 and then close the bracket for the second criteria

Now we're going to actually view this formula and see what we're computing. So close the bracket for the filter for now and press control enter to deploy. There we go. So this works fine

So we can see this returns all the original seven columns from the original data set. We want to go on and filter to return these four columns, date, product, payment type, and the total

So I'm going to come in the top cell and in the formula bar and I'm going to introduce the second filter function. So this is going to be the input value for the array argument of the outermost filter function

And then I'm going to put in a comma on the other side. So for the input argument, now I will actually check the relative position of the columns in the filtered result from the original columns. So I'm going to use the count if function

Now count if requires the range. So I want to check these four columns in I7 to L7 and put in a comma. For the criteria, I want to check the original column and then close the bracket for now

So I'm going to grab this include and I can see this is returning 1, 0, 1, 1, 0, 0, 1. Now the ones simply represent all the columns that we intend to return. The zero simply means or represent all the columns that we do not want to return

So we want to return the date, product, payment type, and the total. So that's all for now. I can close the bracket for the second filter and then control enter to commit. Lovely. So we have all the four columns returned with the total sales that is between 10,000 and 100,000

Now we want to sort this date column. So we want to see from the oldest to the newest. So I can come into the formula bar. Now in order to make it easier for us to understand, I'm going to indent my formula. So I'm going to come in and press alt enter. Now alt enter actually indents the formula

So I'm going to come after the equals and type in the sort function. Now this is going to be the input value for the array argument of the sort function. So I'm going to put in a comma here. Now for the sort index, now by default, the first column internally, in this case, the date column is the index number one. So I can optionally do not specify that argument. So I can type in one or choose to just skip by putting a comma

So for the next argument, the sort order, we want to see from the oldest to the newest. So I can double click on the ascended order and close the bracket for now. And let's control enter to commit. Lovely. So we can see we have the oldest transaction which occurred on the 4th of January 2015

And when I press control down arrow key, we can see the last transaction that occurred on the 4th of December 2022. Lovely. And I can press control up arrow key. So I'm going to come here. Now we are done with the filtering path. Let's see how we can perform the cumulative running total

So I'm going to come into this formula. I'm going to use the amazing let function that allows us to assign calculation results to names. Now we can use to store intermediate calculations and values by defining name inside our formula. So I'm going to press the tab key. And then for the first argument, now for all these, I'm going to store them into what's called a field. This is just a variable name

Now I can use anything you want. So I'm open bracket fields. Okay. I'm putting a comma. So this is going to be the name one. And it's going to be the value we're assigned to the names. Lovely. I want to put in a comma. Now for the name two, I'm just going to define another name. So let me call this one, Carl. You can call it anything you like. Just optional. Up to you. I'm putting a comma

Now for the value that I want to store inside the second name, I'm going to use the amazing H stack function. Now the H stack function stacks arrays into one array. So I'm going to press the tab key. Now the H stack requires the array one as the compulsory argument and array two as optional arguments. So for the array one, I'm going to stack what this field name is delivering. So I'm going to call it field and press the tab key and put in a comma

For the array two, then I can introduce the new scan function. So the scan function. Now the scan function scans an array by applying a lambda function to each value and returns an array that has each intermediate value. So I'm going to press the tab key. The first argument of the scan is the initial value. So I'm going to set these to equal to zero and put in a comma

For the array argument of the scan, now we will actually perform the cumulative run total based on the total column in the field name. So I'm going to use the choose Carl's new function. Now the choose Carl's function returns columns from an array or a reference. So I'm going to press the tab key

Now the first argument is the array. Now I will actually pick the total column in the field name. So I'm going to pass that in and put in a comma. For the column name, we'll actually access the fourth column. This is going to be the first column, second, third, and the fourth. So I'm going to type in four and close the bracket for the choose Carl's function and put in a comma to jump to the function argument of the scan function

And then since we're performing cumulative run total, I'm going to use the sum function. So sum function without opening the brackets and I can close the bracket for the scan function and then close the bracket for the HSTACK function. And then we are back to the let's. Now I'm going to put in a comma. So for the calculation, I'm going to actually pass in this calc name that I defined that is holding all this HSTACK, the scan and the choose Carl's

So I'm going to type in the calc to call the name and close the bracket for the let function. Now I can even go on and indent this formula to make it even more readable. So alt enter. That looks good. And then I can even alt enter this. So that looks good. Now this is the moment of truth. I'm going to press control enter. Amazing

So we've got the cumulative run total in column M. Wonderful. Now on the 4th of January 2015, we generated 59,470 total sales or revenue. And of course this returns the same 59,470. Now on the 3rd of January 2015, we generated 32,043 pounds

And of course, the addition of this two is going to result into this 91,513 and so on and so forth. So this is how we can use this amazing new functions and some of the relatively new functions in Excel to calculate a filtered results, returning some specific columns and at the same time calculates the running total

So I hope you enjoyed this simple Excel formula. So if you do, you can play around if you have all these functions and then you can give this video a thumbs up. Thank you for watching. Bye for now