A question from Elijah at the UCF Accounting Conference. I was showing how to use the UNIQUE function to get a list of products. Elijah asked if I could then get the total sales for each product. Plus a heading row and a total row. With the items sorted high to low.
The final result uses several new Excel functions: UNIQUE, SUMIFS, HSTACK, SORT, VSTACK, and LET.
This lets you create something that is like a pivot table, but there is no need to refresh as the data changes.
Table of Contents
(0:00) Welcome
(0:16) Formulas to Values
(1:08) UNIQUE list of products
(1:35) SUMIFS by product
(2:12) HSTACK and SORT
(2:44) Headings and Total Row
(3:25) VSTACK into final report
(3:53) With fewer helper columns
(5:01) Using LET and no helper columns
(6:45) Thanks & closing
The final result uses several new Excel functions: UNIQUE, SUMIFS, HSTACK, SORT, VSTACK, and LET.
This lets you create something that is like a pivot table, but there is no need to refresh as the data changes.
Table of Contents
(0:00) Welcome
(0:16) Formulas to Values
(1:08) UNIQUE list of products
(1:35) SUMIFS by product
(2:12) HSTACK and SORT
(2:44) Headings and Total Row
(3:25) VSTACK into final report
(3:53) With fewer helper columns
(5:01) Using LET and no helper columns
(6:45) Thanks & closing
Transcript of the video:
Hey, welcome back to MrExcel netcast. I'm Bill Jelen.
Today, we're going to create a sorted summary with totals and headings without using a pivot table.
Great question during my live Excel Power Excel at the UCF Accounting Conference from Elijah. Now, before we get to Elijah's question I just have to tell you that earlier in the session he came up with a faster way.
I was showing how you can convert these formulas to values, right click, drag right, drag left, let go, copy here as values only.
And he's like, "Hey, no, there's a faster way than that".
And of course, if you know this and use this all the time it's super easy, control C to copy, and then alt E, S, V, Enter and I can definitely do control C alt E, S, V as fast as I can do grabbing the mouse.
So, a lot of cred to Elijah for knowing that.
I was in the process of showing the UNIQUE function to get a unique list of these products.
He said, "Oh no, wait, can you build a summary of how much was sold for each of those products?" And let's add a total to the bottom and headings at the top, sort it high to low.
And I'll have to admit that I “choked” during the seminar.
Whatever formula I built in step two did not work at that moment so I said to Elijah, "Hey. well, let's take this to YouTube". I'll post it on YouTube on Monday.
So there's eight steps we're going to do to get from this table to this little table here.
I'm going to start out with actually what I was showing at the moment, the UNIQUE function.
So we take the unique of that range and we got a unique list of products.
All right, step two.
Now that we have that unique list of products, can we get the sales for each of those products?
So SUMIFS, what's the sum range, it's right there, F3 to F28, what's the criteria range, go look through the products and see if it's equal to H3#, that hash saying return all the results.
This is the formula I tried live during the seminar, and obviously I screwed it up, no recording of what I did, so I'm not sure how it didn't work. All right.
So now we have in two separate arrays, the products and the sales for those products, I want to sort those high to low.
And in order to do that, I'm going to have to HSTACK it.
So equal HSTACK of this array, hash, comma, this array, hash.
And that gives me one array with all of the answers.
Now that I have that single array, then I can do a sort and whatever I want to sort by, I want to sort by the second column and in descending order like that. All right.
So, we're back to a single array now, sorted high to low.
That's great. Let's try and improve this a little bit.
Let's add headings to the top and totals to the bottom.
Headings to the top, well that's easy, that's just these headings we're going to copy right here.
Little array formula to bring that back.
And then, for the totals, all right, well, let's see. There's two things I want here.
I want the word Total, and I want the total of the sales.
So I'm going to try this, equal HSTACK, a horizontal stack.
So side by side, first array is just going to be the word Total.
Yep, we can pass it a scaler as an array. That's awesome.
And then the SUM of all of these sales over here, I3#, and that is going to generate the total row. So we built just on the fly, the total row.
Step six, almost done now: a VSTACK of this array, comma, this array, comma, and this array, putting a hash after each one. And there we go.
So headings, data sorted high to low, and then a total row at the bottom.
That's beautiful. Now I'm going to add step seven and eight.
How can we do this with fewer columns?
All right, so trying to embed everything in here, we're going to VSTACK the heading.
So I can just point to those headings - I don't have to do it in a separate little formula.
And then the sort of the HSTACK of H3#, I3#, sort that, and then HSTACK the total row right on the fly.
So I'm VSTACKing three things together there, all of the rows in the middle are the data.
And of course the beautiful thing here, let's just take and add a new item.
So let's add Mango, which is not currently in the list.
You see that everything adjusts here, which is beautiful, we get extra rows, the total moves down, life is great.
But at this point I still have to have my original list of products and my original list of sales by the host products, the H3 and the I3.
Is there a way that I can get this summary report without using those at all?
And that is going to require a function that I did not cover in the Power Excel Seminar at UCF.
Great function called LET, and in LET we're able to reuse variables.
So we start out with equal LET and we create a variable called product, which is the unique list of products, comma, Alt+Enter to go to a new line.
A new variable called Sales.
And this is awesome, see, the Sales variable is allowed to use a SUMIF and reuse the product array.
Comma, Alt+Enter to go to new line.
Now you can define as many variables as you need, but that's really all I need is the product array and the sales array.
From there I'm now able to do my formula where I do the VSTACK of the headings.
And then the next thing that we're going to stack in there is the sort of the HSTACK of product and sales sorting by the second column. That's sales, in descending order, comma.
And then the last thing that I'm going to VSTACK in is the word total and the sum of sales reusing all of that, right?
So that formula there works great.
Again, let's come back here and add Mango and it automatically extends and grows. So there you are, there's the formula.
I certainly would not have been able to build that live in front of a studio audience.
I had to work it through, so great question for Elijah and thanks to him and everyone at the seminar, giving me some time to kind of walk away and puzzle through this.
A lot of this is new.
I mean, VSTACK and HSTACK are still in Insiders Fast right now, but even LET, a year and a half old is not as familiar to me as VLOOKUP or XLOOKUP or INDEX and MATCH.
Well, hey, thanks again to the UCF Accounting Department for inviting me to speak at their seminar.
Thanks to Elijah for this great question and for his tip with control C and Alt+E S V, And thanks to you for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please down below, like subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
Now, if you love Excel, check out my new courses on the Retrieve platform.
They're video courses, but you just type what you're looking for.
It takes you right to that spot in the video.
And there's a complete transcript in several languages. It's a super fast way to learn.
Today, we're going to create a sorted summary with totals and headings without using a pivot table.
Great question during my live Excel Power Excel at the UCF Accounting Conference from Elijah. Now, before we get to Elijah's question I just have to tell you that earlier in the session he came up with a faster way.
I was showing how you can convert these formulas to values, right click, drag right, drag left, let go, copy here as values only.
And he's like, "Hey, no, there's a faster way than that".
And of course, if you know this and use this all the time it's super easy, control C to copy, and then alt E, S, V, Enter and I can definitely do control C alt E, S, V as fast as I can do grabbing the mouse.
So, a lot of cred to Elijah for knowing that.
I was in the process of showing the UNIQUE function to get a unique list of these products.
He said, "Oh no, wait, can you build a summary of how much was sold for each of those products?" And let's add a total to the bottom and headings at the top, sort it high to low.
And I'll have to admit that I “choked” during the seminar.
Whatever formula I built in step two did not work at that moment so I said to Elijah, "Hey. well, let's take this to YouTube". I'll post it on YouTube on Monday.
So there's eight steps we're going to do to get from this table to this little table here.
I'm going to start out with actually what I was showing at the moment, the UNIQUE function.
So we take the unique of that range and we got a unique list of products.
All right, step two.
Now that we have that unique list of products, can we get the sales for each of those products?
So SUMIFS, what's the sum range, it's right there, F3 to F28, what's the criteria range, go look through the products and see if it's equal to H3#, that hash saying return all the results.
This is the formula I tried live during the seminar, and obviously I screwed it up, no recording of what I did, so I'm not sure how it didn't work. All right.
So now we have in two separate arrays, the products and the sales for those products, I want to sort those high to low.
And in order to do that, I'm going to have to HSTACK it.
So equal HSTACK of this array, hash, comma, this array, hash.
And that gives me one array with all of the answers.
Now that I have that single array, then I can do a sort and whatever I want to sort by, I want to sort by the second column and in descending order like that. All right.
So, we're back to a single array now, sorted high to low.
That's great. Let's try and improve this a little bit.
Let's add headings to the top and totals to the bottom.
Headings to the top, well that's easy, that's just these headings we're going to copy right here.
Little array formula to bring that back.
And then, for the totals, all right, well, let's see. There's two things I want here.
I want the word Total, and I want the total of the sales.
So I'm going to try this, equal HSTACK, a horizontal stack.
So side by side, first array is just going to be the word Total.
Yep, we can pass it a scaler as an array. That's awesome.
And then the SUM of all of these sales over here, I3#, and that is going to generate the total row. So we built just on the fly, the total row.
Step six, almost done now: a VSTACK of this array, comma, this array, comma, and this array, putting a hash after each one. And there we go.
So headings, data sorted high to low, and then a total row at the bottom.
That's beautiful. Now I'm going to add step seven and eight.
How can we do this with fewer columns?
All right, so trying to embed everything in here, we're going to VSTACK the heading.
So I can just point to those headings - I don't have to do it in a separate little formula.
And then the sort of the HSTACK of H3#, I3#, sort that, and then HSTACK the total row right on the fly.
So I'm VSTACKing three things together there, all of the rows in the middle are the data.
And of course the beautiful thing here, let's just take and add a new item.
So let's add Mango, which is not currently in the list.
You see that everything adjusts here, which is beautiful, we get extra rows, the total moves down, life is great.
But at this point I still have to have my original list of products and my original list of sales by the host products, the H3 and the I3.
Is there a way that I can get this summary report without using those at all?
And that is going to require a function that I did not cover in the Power Excel Seminar at UCF.
Great function called LET, and in LET we're able to reuse variables.
So we start out with equal LET and we create a variable called product, which is the unique list of products, comma, Alt+Enter to go to a new line.
A new variable called Sales.
And this is awesome, see, the Sales variable is allowed to use a SUMIF and reuse the product array.
Comma, Alt+Enter to go to new line.
Now you can define as many variables as you need, but that's really all I need is the product array and the sales array.
From there I'm now able to do my formula where I do the VSTACK of the headings.
And then the next thing that we're going to stack in there is the sort of the HSTACK of product and sales sorting by the second column. That's sales, in descending order, comma.
And then the last thing that I'm going to VSTACK in is the word total and the sum of sales reusing all of that, right?
So that formula there works great.
Again, let's come back here and add Mango and it automatically extends and grows. So there you are, there's the formula.
I certainly would not have been able to build that live in front of a studio audience.
I had to work it through, so great question for Elijah and thanks to him and everyone at the seminar, giving me some time to kind of walk away and puzzle through this.
A lot of this is new.
I mean, VSTACK and HSTACK are still in Insiders Fast right now, but even LET, a year and a half old is not as familiar to me as VLOOKUP or XLOOKUP or INDEX and MATCH.
Well, hey, thanks again to the UCF Accounting Department for inviting me to speak at their seminar.
Thanks to Elijah for this great question and for his tip with control C and Alt+E S V, And thanks to you for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please down below, like subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
Now, if you love Excel, check out my new courses on the Retrieve platform.
They're video courses, but you just type what you're looking for.
It takes you right to that spot in the video.
And there's a complete transcript in several languages. It's a super fast way to learn.
