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 of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.