Sales by Region & Team

Dueling ExcelPodcast, Episode 188: Sales Team Report By Region.

Bill:   Hey. Welcome back. It's time for another Dueling Excel Podcast. I'm Bill Jelen from MrExcel. I’ll be joined by Mike Girvin from ExcelIsFun. This is our episode 188, Sales Team Report By Region.

Alright, so, here's the question we have, a dataset over here with various sales reps, how much their sales were by region, and some people have sales in both regions, and then the company has organized those 16 sales reps into these four sales teams, and we're trying to figure out, for each sales team, how much revenue they had.

Alright. So, my approach to this is, you know, I don't like this format here. I'm going to rearrange that format into some sort of a table, a little hierarchy here, that shows for each team who the sales reps are and then, if provided we’re in Excel 2013 or Excel 2016 using Windows and not a Mac, then we can make use of the data model, and, in order to do this, we have to take each of these tables and FORMAT AS TABLE which is CONTROL+T. So, there's the first table which they call Table 8 and the second table which they will call Table 9. I'm going to rename these. I'm going to take the first one and I’m going to call it SALES TABLE and I'm going to take the second one and I'm going to call it TEAM HIERARCHY, like that. Alright.

Now, check this out. Starting in Excel 2013, on the INSERT tab, we create a PIVOT TABLE from the first data set but we say ADD THIS DATA TO THE DATA MODEL which is the boringest way to let you know that you actually have the Power Pivot engine sitting behind Excel 2013. Even if you're not paying for Power Pivot, even if just have the base level Excel Office 365 or Excel, you have that. Alright, so, here's our new report and what I'm going to do is I definitely want to report by REGION, so there's the REGIONS, and I want to see the total SALES but I want to look at this by sales team. Check this out. I’m going to choose ALL and that gives me the other tables in this group, including TEAM HIERARCHY. I'll take the TEAM and move it across the COLUMNS.

Now, the first thing that's going to happen here is we get the wrong answers. That's very, very normal to get the wrong answers. So, what we're going to do is we're going to click CREATE. If you're in ’16, you can AUTO-DETECT. Let's pretend they're in Excel 2013 where we go to our SALES TABLE. There's a field there called SALES REP and it's related to the HIERARCHY, field called SALES REP, click OK, and we have the correct answers. Mike, let's see what you have.

Mike: Thanks, MrExcel. Yes, the data model is an awesome way to go with two different tables to build one pivot table and that's really my preferred method, but if you had to do it with a formula and you needed to have SALES TEAM at the top of each column like this, that means, with the formula, we literally have to look through this data set and, for each record, I have to ask, is the SALES REP = to Gigi or Chin or Sandy or Sheila, and then, if it's a net sale, I have to say, and is the region North America.

Well, we can do that. We can do an AND logical test and an OR logical test in the SUMIFS function. SUM_RANGE, those are all the numbers, so I'm going to click in the top cell, CONTROL+SHIFT+DOWNARROW+F4 , CRITERIA_RANGE, I'm going to highlight the entire SALESREP column, CONTROL+SHIFT+DOWNARROW+F4 ,. Now, normally we put a single item like JUNE SALES REP into criteria. That tells SUMIFS to spit out one answer for JUNE, but, if I highlight 4 different cells -- 1 for each sales rep -- we're instructing SUMSIFS to do a SUMIF for each individual sales rep.

Now, when I copy this formula down, I need it locked, but I copy it to the side, it needs to move. So, I have to hit the F4 key 1, 2 times, lock the row, but not the column. Now I'm going to ). This is a function argument array operation. That's the function argument. The fact that we have multiple items means it's an array operation. So, when I click at the end and hit F9, SUMIFS obeyed us. It spit out the total amount for June, Sioux, Poppi, and Tyrone. [=SUMIFS($B$4:$B$45,$A$4:$A$45,F$4:F$7)]

Now, we need to further limit those amounts by adding an AND condition. We really need it to be June and North America or Sioux and North America or Poppi and North America, and so on. CONTROL+Z. We simply extend , CRITERIA RANGE 2. Now we need to look through the REGION column. CONTROL+SHIFT+DOWNARROW+F4 , and I'm going to click on the single condition, F4 1, 2, 3 times to lock the column but not the row. If I click at the end and F9, those are the totals for each one of our sales rep in North America. When we copy it down, SUMIFS will deliver the total for each sales rep for South America. [=SUMIFS($B$4:$B$45,$A$4:$A$45,F$4:F$7,$C$4:$C$45,$E8)]

Notice it's just SUMIFS delivering multiple numbers we need to add. CONTROL+Z. So, I could put it into this SUM function but the SUM function NUMBER 1 argument will not calculate this array operation correctly without using CONTROL+SHIFT+ENTER. So, I'm going to cheat and use SUMPRODUCT. Now, normally, SUMPRODUCT takes multiple arrays and multiplies them -- that's the PRODUCT part -- and then adds them, but I'm just going to use ARRAY1 and just use the SUM part of SUMPRODUCT, ), CONTROL+ENTER, copy it down and over to the side, and since I got lots of crazy cell references, I'm going to come to the last one in F2 and, sure enough, it's got all of the cells and ranges correct. Alright. I'm going to throw back to MrExcel. [=SUMPRODUCT(SUMIFS($B$4:$B$45,$A$4:$A$45,F$4:F$7,$C$4:$C$45,$E8))]

Bill:   What? That's crazy. Mike. Point to Mike. Oh my gosh. Putting a range of values in SUMIFS and then sending it into SUMPRODUCTS and make it treat it like an ARRAY. Hey, that is wild. We should just stop right there. Point to Mike.

Alright. Let's go back to my method but pretend that you don't have Excel 2013. You're back in Excel 2010 or, worse, Excel for the Mac. I mean, it says it's Excel. I don't know. It just drives me crazy what the Mac can or can't do. So, we’re going to take my HIERARCHY TABLE over here, and, because VLOOKUP can't look to the left, I'm going to take the SALES REP information, CONTROL+X, and paste. Yeah, I know I can do index and match. I'm not in the mood to do index and match today. Alright, so, it's really simple. Here, =VLOOKUP, take that SALESREP name over there, and we will F4 , 2 , EXACTMATCHFALSE like that, double click to copy that down. [=VLOOKUP(A4,$F$4:$G$19,2,FALSE)]

Now that we have all this data back in one table, simple little INSERT, PIVOT TABLE. Even if you don't have the check box at this stage of the data model, we can build our report with SALES TEAM going across, REGION going down, and SALES like that. You can even, here, let's reverse these, REGION across and add the SALES REP in like that, in case you want to see who the sales reps were, and if, by default, if you don't want that, we could just collapse the whole group. So, from here, I go to the ANALYZE tab and collapse. Alright. So, there's our sales teams by region, and then, if someone wants to say, well, who was SALES TEAM 2, we can open that up individually, something like that. Mike, you got another one?

Mike: Still got to love VLOOKUP. It does so many amazing things and, yes, I agree with you MrExcel. Excel for the Mac, that's not even Excel, is it? Alright. Okay. I have another method but I'm going to have to jump over to a different workbook. So, I just have the same two data sets and I've converted them to Excel tables and named them. There's the SALES table, there's the TEAM table, and I like your Power Pivot option so much I'm going to steal that but do it a slightly different way because, as you say, if you have Excel 2013 or later, you have the Power Pivot data model there, but it gets even better. On the DATA ribbon tab -- and I have Excel 2016 -- if you have the RELATIONSHIPS button, you can just build the relationship as if it was a VLOOKUP between these two tables and it will automatically send it to the data model.

So, here's the MANAGE RELATIONSHIPS. I'm going to click NEW. I'm going to select SALES table, SALES REP. This, in essence, is our lookup value, right, and then I'm going to select the lookup table dTEAM, and the SALES REP. This is the lookup table so it can look up SALES REP and return the SALES TEAM, but there's no VLOOKUP column. It simply is two tables in our pivot table field list. Yeah. Look at that, the relationships, when I click OK, it’s sending it to the data model.

Now I'm going to click in a cell off to the side ALT+N+V to open up CREATE PIVOTTABLE dialog box and -- look at that -- it already assumes I want the data model because there's stuff in the data model. Now I click OK and I have my two tables right there. I'm going to click the drop-down, SALES TEAM to ROW, SALES REP down below ROWS, and then SALES from the SALES TABLE down to VALUES. ROW LABELS. I don't like that so I'm going to go up to SHOW IN TABULAR, right click, NUMBER FORMATTING, something like CURRENCY, click OK.

Now, just as MrExcel said, we can collapse this if we do not want to see the SALES REP, and then drag REGION down to COLUMNS, and, just like that, we have all of our sales teams’ totals for each region. I could even open this up. Whether you access the data model either through the check box in CREATE PIVOTTABLES dialog box or simply DATA, RELATIONSHIPS, that is the way to go. So fast and easy, and we can pull fields from two different tables. Alright, I'm going to throw it back to MrExcel.

Bill:   Whoa, Mike, the RELATIONSHIPS out here on the DATA tab, I'm sure I've never noticed that and I guess, in my defense, in the smaller version of Excel here, it doesn't have a word on it. Just looks like a tiny little icon and I realize it was new. That is super, super cool.

Alright. Let’s just do one more here. I'm going to use power query. So, on the DATA tab, GET & TRANSFORM DATA. FROM A TABLE, I select the first table, and I want to take this REGION field and I'm going to pivot it, so I'm going to create a pivot table right here in power query. I'd be careful here. The values are in the SALES area. Click OK. So, now, for each SALES REP, we have their sales to NORTH AMERICA and SOUTH AMERICA, and I'm going to call this ByRep. BYREP. I'll call it BYREP, and then HOME, CLOSE & LOAD, but I'm not going to CLOSE & LOAD to the workbook. I'm going to say ONLY CREATE CONNECTION, like that.

Alright. Then, I'll come to the second one and say that I'm going to create a query FROM A TABLE, alright, and this is just going to stay exactly the way it is. We'll call this TEAMS, and CLOSE & LOAD, CLOSE & LOAD to ONLY CREATE CONNECTION, like that.

Alright. So, now we have two different reports here and I'm going to say that I want to create a COMBINE QUERY, a MERGE QUERY, and my first query is going to be called BYREP, and then I'm going to look up into the TEAMS query. Now, this part is the part that is not intuitive at all. Click on SALES REP here, click on SALES REP here, and we want ALL FROM FIRST, MATCHING FROM SECOND. Click OK. Alright. So, now, here's all of our SALES REP information, what they sold in North America, what they sold in South America, and use the expand icon here, and all we want to get is the TEAM information. I just want to call it TEAM. I don't want to call it TEAMS.TEAM. That would be crazy.

Alright. At this point, we no longer need the SALES REP information. I'll remove that column. I'll take the TEAM and move it over to the left, and then -- check this out -- GROUP BY. We're going to GROUP BY the TEAM and the NEW COLUMN NAME is going to be called NORTH AMERICA, the OPERATION is going to be SUM, the NORTH AMERICA COLUMN, and then we'll add a second one called SOUTH AMERICA, SUM, the SOUTH AMERICA COLULMN. There we go. GROUP BY TEAM, two columns, and we have our information here.

Let's order this. So, on the HOME tab, we want to SORT A to Z. SALES TEAM 1, 2, 3, 4. There's our NORTH AMERICA. There's our SOUTH AMERICA. Now, finally, we'll CLOSE & LOAD and we have our results, and -- check this out -- that's even cooler than that. So, if I go back to BILLPQ and we take POPPI and we move POPPI to SALES TEAM 2 and then come back to our results out here, alright, so, SALES TEAM 2, we should see these numbers increase. Come here and click the refresh icon, and those numbers changed, right? How cool? How cool is that?

Alright, so, wrap up. The goal today, we're going to build a sales report by region and team. The original data has sales rep in region and then there's a lookup table -- in my opinion, badly shaped -- that organizes sales reps into teams. So, my method reshaped that data into a team hierarchy data, make both ranges into Control+T tables, create a pivot table, adding the data to the data model, and then create a relationship. Mike’s method: use SUMIFS where the Criteria2 to field is an array -- didn’t know you could do that -- and then the SUMPRODUCT function. My third method: rearrange the hierarchy table so sales rep is on the left and then do a VLOOKUP building a pivot table. Mike's method: use the RELATIONSHIP icon to build a relationship first, and then a pivot table from the workbook data model. And then, the fifth version -- the no VLOOKUP-no pivot table version in case you're afraid of both of those -- power query. Add the lookup table as a connection only, add the original table as a lookup only, doing the pivot right there to get North America and South America,  merge those two tables, group by, and then group by within power query, and you can refresh.

Alright. Well, hey. I want to thank you for stopping by for this very long Dueling Excel Podcast. We'll see you next time for another episode from MrExcel and ExcelIsFun.