MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sales by Region & Team


November 03, 2017 - by Bill Jelen

Sales by Region & Team

You have a report showing sales for 16 sales reps. Each sales rep belongs to a team. How can you create a report showing total sales for each team?


Watch Video

  • Build a Sales Report by Region and Team
  • Original data has sales rep and region
  • A second (badly shaped) table organizes the sales reps in to teams
  • Bill method 1: Re-shape the team hierarchy data. Make both ranges into Ctrl + T tables
  • Create a pivot table, adding the data to the data model. Pull Team from second table.
  • Create a relationship
  • Mike Method2: Build a SUMIFS where the Criteria2 field is an array!
  • Pass the SUMIFS in to the SUMPRODUCT function
  • Bill Method 3: Rearrange the hierarchy table so sales rep is on the left.
  • Add a VLOOKUP to the original data
  • Build a pivot table
  • Mike Method 4: Use the Relationship icon on the Data tab of the ribbon
  • When you create the pivot table, choose Use this Workbook's Data Model
  • Bill Method 5: Power Query. Add the lookup table as a Connection Only
  • Add the original table as a lookup only
  • Merge those two tables, group by to produce the final report

Auto-Generated Transcript

  • hey welcome back it's time for another
  • dueling Excel podcast I'm Bill Jelen
  • from MrExcel be joined by my career
  • from Excel as one this is our episode
  • 188 sales team report by region all
  • right 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 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 all right 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 were in Excel
  • 2013 or Excel 2016 using Windows and not
  • a Mac and then we can make use of the
  • data model and I don't need to do this
  • we're to take each of these tables and
  • format as table which is ctrl T so
  • there's the first table which they call
  • table 8 and the second table which they
  • will call table 9 I'm gonna rename these
  • I'm gonna take the first one and I'll
  • 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 are so here's our
  • 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 because I'm
  • gonna choose all and that gives me the
  • other tables in this in this group
  • including team hierarchy I'll take the
  • team and move it across the columns now
  • the first thing that's gonna happen here
  • is we get the wrong answers that's very
  • very normal to get the wrong answer so
  • what we're gonna 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
  • 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 as the
  • sales rep equal to GG or Jin or sandy or
  • Sheila and then if it's a net sell I
  • have to say
  • Ann is the region North America well we
  • can do that we can do an and logical
  • test and an or logical test in the sum
  • ifs function some range those are all
  • the numbers so I'm going to click in the
  • top cell ctrl shift down arrow f4 comma
  • criteria range I'm gonna highlight the
  • entire sales rep column ctrl shift down
  • arrow f4 comma now normally we put a
  • single item like June sales rep into
  • criteria that tells some ifs to spit out
  • one answer for June but if I highlight
  • four different cells one for each sales
  • rep we're instructing some ifs to do a
  • summit for each individual sales rep now
  • when I copy this formula down they 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 close
  • parenthesis 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 some ifs obeyed us
  • it spit out the total amount for Junsu
  • poppy and Tyrone now we need to further
  • limit those amounts by adding an and
  • condition we really need it to be June
  • and North America or su and North
  • America or poppy and North America and
  • so on control-z
  • we simply extend comma criteria range -
  • now we need to look through the region
  • column ctrl shift down arrow f4 comma
  • and I'm gonna click on the single
  • condition at four one two three times to
  • lock the column but not the row if I
  • click at the N and f9 those are the
  • totals for each one of our sales rep in
  • North America when we copy it down some
  • ifs will deliver the total for each
  • sales rep for South America notice it's
  • just some it's delivering multiple
  • numbers we need to add control Z so I
  • could put it into this sum function but
  • the sum function number one argument
  • will not calculate this array operation
  • correctly without using ctrl shift enter
  • so I'm gonna cheat and use some product
  • now normally some product takes multiple
  • arrays and multiplies them that's the
  • product part and then adds them but I'm
  • just gonna use array one and just use
  • the sum part of some product close
  • parentheses control enter copy it down
  • and over to the side and since I got
  • lots of crazy cell references I'm gonna
  • come to the last one and f2 and sure
  • enough it's got all of the cells and
  • ranges correct all right I'm gonna throw
  • back to MrExcel what that's crazy Mike
  • point to Mike oh my gosh
  • putting a range of values and some ifs
  • and then sending it into some products
  • and make it treat it like an hour
  • 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
  • doesn't doesn't it just drives me crazy
  • what the Mac can or can't do so we're
  • taking 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 all
  • right so it's really simple here equal
  • vlookup take that sales rep name over
  • there and we will 4 comma 2 comma exact
  • match false like that double click to
  • copy that down 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 for at the 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
  • you don't want that we could just
  • collapse the whole group so from here
  • I'm gonna lies tab and collapse alright
  • so there's our sales teams by region and
  • then if someone wants to say well who
  • was sales team to we can open that up
  • individually something like that Mike
  • you got another one still got a 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 gonna 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 if you have 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 gonna select
  • sales table sales rep this in essence is
  • our lookup value right and then I'm
  • going to select the lookup table Det and
  • the sales rep this is the lookup table
  • so it can lookup sales rep and return
  • the sales team but there's no vlookup
  • column it simply is two tables in our
  • previous 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 + B to open up create pivot table
  • dialog box and look at that it already
  • assumes I want the data model cuz
  • there's stuff in the data model now I
  • click OK and I have my two tables right
  • there I'm gonna click the drop down
  • sales team to Rome 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 can
  • even open this up whether you access the
  • data model either through the check box
  • in create pivot tables dialog box are
  • simply data relationships that is the
  • way to go so fast and easy and we can
  • pull fields
  • two different tables all right I'm gonna
  • throw it back to MrExcel
  • oh my relationship 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 all right let's just do
  • one more here I'm gonna use power query
  • so on the data tab get in 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 gonna
  • create a pivot table right here in power
  • query I'd be careful here the values are
  • in the sales area click okay so now for
  • each sales rep we have their sales to
  • North America and South America and I'm
  • gonna call this my rep by Rep I'll call
  • it by rep and then home closing load I'm
  • not going to close and load to the
  • workbook I'm going to say only create a
  • connection like that all right then I'll
  • come to the second one and say that I'm
  • gonna create a query from a table all
  • right and this is just going to stay
  • exactly the way it is we'll call this
  • teams and close and load close and low
  • to only create a connection like that
  • all right so now we have two different
  • reports here and I'm going to say that I
  • want to create a combined query merge
  • query and my first query is going to be
  • called by rep and then I'm going to look
  • up into the team's query now this part
  • is the part that is not intuitive at all
  • click on sales are up here click on
  • sales were up here and we want all from
  • the first matching from the second click
  • okay
  • all right so now here's all of our sales
  • rep information what they sold in North
  • America where 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 a team I don't want to
  • call it team's team that would be crazy
  • all right 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 a group on
  • a team and the new
  • the new column name is going to call it
  • North America the operation is going to
  • be some the North America column and
  • then we'll add a second one called South
  • America well some the South America
  • column they're a group by team two
  • columns and we have her information here
  • let's order this so on the home tab we
  • want to sort A to Z sales team one two
  • three four there's our North America
  • there's our South America now finally
  • we'll close and load and we have our
  • results and and check this out that's
  • even cooler than that so if I go back to
  • build power query and we take Poppi and
  • we move papi to sales team two and then
  • come back to our results set here all
  • right so sales team - we should see
  • these numbers increase come here and
  • click the Refresh icon and those numbers
  • changed right how cool how cool is that
  • all right so wrap up that goal today
  • we're gonna build a sales report by
  • region and team the original data has
  • sales rep in region and there's a lookup
  • table in my opinion badly shaped that
  • organizes the sales reps into teams so
  • my method reshaped that data into a team
  • hierarchy data make both ranges into
  • control-t tables
  • great a pivot table adding the data to
  • the data model and then create a
  • relationship mics method use some ifs
  • with a criteria to field is a now tradin
  • you could do that and then the
  • sum-product function my third method
  • rearranged the hierarchy table so sales
  • reps on the left and then to 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 and
  • the lookup table as a connection only I
  • had the original table as a lookup only
  • doing the
  • hibbett right there to get north
  • american south america merge those two
  • tables group by and then group yet group
  • by within power query and you can
  • refresh all right well hey i want to
  • thank you for stopping by but there's a
  • very long dueling excel podcast we'll
  • see you next time for another episode
  • from MrExcel and excel is fun

Download File

Download the sample file here: Duel188.xlsm

Title Photo: MichaelGaida / Pixabay


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.