{"text_format":"1","workbook":"xlsx","n_sheets":9,"sheets":["Title","Question","BillTry","MikeTry","BillTry2","MikeQuery","Sheet2","BillPQ","WrapUp"],"not_rendered":{"charts":0,"images":6,"macros":0,"external_links":1,"defined_names":9,"comments":0,"pivot_tables":2},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Title","index":0,"n_rows":25,"n_cols":13,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,188]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Image credit: MichaelGaida / Pixabay"]

{"sheet":"Question","index":1,"n_rows":45,"n_cols":9,"rows_kept":45,"cut":false,"hidden":false,"merged":[]}
["Need a Sales Team and Region Report."]
[]
["Sales Rep","Sales","Region",null,null,"Sales Team 1","Sales Team 2","Sales Team 3","Sales Team 4"]
["Kip",457,"North America",null,null,"June","Gigi","Fred","Irene"]
["June",368,"North America",null,null,"Sioux","Chin","Kip","Al"]
["Gigi",725,"South America",null,null,"Poppi","Sandi","Abdi","Mel"]
["Mel",736,"South America",null,null,"Tyrone","Shelia","Pham","Mo"]
["Tyrone",606,"South America",null,"North America"]
["Chin",1418,"South America",null,"South America"]
["Abdi",1110,"South America"]
["Sandi",1489,"North America"]
["June",175,"North America"]
["Shelia",1354,"South America"]
["Pham",132,"North America"]
["Gigi",449,"South America"]
["Mo",1333,"North America"]
["Sandi",1571,"North America"]
["Chin",1553,"North America"]
["Tyrone",1196,"South America"]
["June",679,"North America"]
["Pham",221,"South America"]
["Pham",1097,"North America"]
["Gigi",473,"North America"]
["Tyrone",573,"South America"]
["Gigi",1160,"South America"]
["June",1268,"North America"]
["Sandi",1635,"North America"]
["Mel",152,"North America"]
["Poppi",603,"North America"]
["Irene",458,"North America"]
["Sioux",741,"South America"]
["Fred",486,"North America"]
["Tyrone",1227,"North America"]
["Poppi",981,"North America"]
["Fred",798,"North America"]
["Sioux",1191,"South America"]
["Gigi",363,"South America"]
["Abdi",1332,"North America"]
["Mo",1289,"South America"]
["Fred",1625,"North America"]
["Pham",1261,"South America"]
["Mo",556,"North America"]
["Sandi",1441,"North America"]
["Fred",337,"South America"]
["Fred",284,"North America"]

{"sheet":"BillTry","index":2,"n_rows":45,"n_cols":13,"rows_kept":45,"cut":false,"hidden":false,"merged":[]}
["Need a Sales Team and Region Report."]
[]
["Sales Rep","Sales","Region",null,"Team","Sales Rep"]
["Kip",457,"North America",null,"Sales Team 1","June",null,"Sum of Sales","Team"]
["June",368,"North America",null,"Sales Team 1","Sioux",null,"Region","Sales Team 1","Sales Team 2","Sales Team 3","Sales Team 4","Grand Total"]
["Gigi",725,"South America",null,"Sales Team 1","Poppi",null,"North America",5301,8162,6211,2499,22173]
["Mel",736,"South America",null,"Sales Team 1","Tyrone",null,"South America",4307,5469,2929,2025,14730]
["Tyrone",606,"South America",null,"Sales Team 2","Gigi",null,"Grand Total",9608,13631,9140,4524,36903]
["Chin",1418,"South America",null,"Sales Team 2","Chin"]
["Abdi",1110,"South America",null,"Sales Team 2","Sandi"]
["Sandi",1489,"North America",null,"Sales Team 2","Shelia"]
["June",175,"North America",null,"Sales Team 3","Fred"]
["Shelia",1354,"South America",null,"Sales Team 3","Kip"]
["Pham",132,"North America",null,"Sales Team 3","Abdi"]
["Gigi",449,"South America",null,"Sales Team 3","Pham"]
["Mo",1333,"North America",null,"Sales Team 4","Irene"]
["Sandi",1571,"North America",null,"Sales Team 4","Al"]
["Chin",1553,"North America",null,"Sales Team 4","Mel"]
["Tyrone",1196,"South America",null,"Sales Team 4","Mo"]
["June",679,"North America"]
["Pham",221,"South America"]
["Pham",1097,"North America"]
["Gigi",473,"North America"]
["Tyrone",573,"South America"]
["Gigi",1160,"South America"]
["June",1268,"North America"]
["Sandi",1635,"North America"]
["Mel",152,"North America"]
["Poppi",603,"North America"]
["Irene",458,"North America"]
["Sioux",741,"South America"]
["Fred",486,"North America"]
["Tyrone",1227,"North America"]
["Poppi",981,"North America"]
["Fred",798,"North America"]
["Sioux",1191,"South America"]
["Gigi",363,"South America"]
["Abdi",1332,"North America"]
["Mo",1289,"South America"]
["Fred",1625,"North America"]
["Pham",1261,"South America"]
["Mo",556,"North America"]
["Sandi",1441,"North America"]
["Fred",337,"South America"]
["Fred",284,"North America"]

{"sheet":"MikeTry","index":3,"n_rows":45,"n_cols":9,"rows_kept":45,"cut":false,"hidden":false,"merged":[]}
["Need a Sales Team and Region Report."]
[]
["Sales Rep","Sales","Region",null,null,"Sales Team 1","Sales Team 2","Sales Team 3","Sales Team 4"]
["Kip",457,"North America",null,null,"June","Gigi","Fred","Irene"]
["June",368,"North America",null,null,"Sioux","Chin","Kip","Al"]
["Gigi",725,"South America",null,null,"Poppi","Sandi","Abdi","Mel"]
["Mel",736,"South America",null,null,"Tyrone","Shelia","Pham","Mo"]
["Tyrone",606,"South America",null,"North America",{"$f":"SUMPRODUCT(SUMIFS($B$4:$B$45,$C$4:$C$45,$E8,$A$4:$A$45,F$4:F$7))","v":5301},8162,6211,2499]
["Chin",1418,"South America",null,"South America",4307,5469,2929,2025]
["Abdi",1110,"South America"]
["Sandi",1489,"North America"]
["June",175,"North America"]
["Shelia",1354,"South America"]
["Pham",132,"North America"]
["Gigi",449,"South America"]
["Mo",1333,"North America"]
["Sandi",1571,"North America"]
["Chin",1553,"North America"]
["Tyrone",1196,"South America"]
["June",679,"North America"]
["Pham",221,"South America"]
["Pham",1097,"North America"]
["Gigi",473,"North America"]
["Tyrone",573,"South America"]
["Gigi",1160,"South America"]
["June",1268,"North America"]
["Sandi",1635,"North America"]
["Mel",152,"North America"]
["Poppi",603,"North America"]
["Irene",458,"North America"]
["Sioux",741,"South America"]
["Fred",486,"North America"]
["Tyrone",1227,"North America"]
["Poppi",981,"North America"]
["Fred",798,"North America"]
["Sioux",1191,"South America"]
["Gigi",363,"South America"]
["Abdi",1332,"North America"]
["Mo",1289,"South America"]
["Fred",1625,"North America"]
["Pham",1261,"South America"]
["Mo",556,"North America"]
["Sandi",1441,"North America"]
["Fred",337,"South America"]
["Fred",284,"North America"]

{"sheet":"BillTry2","index":4,"n_rows":45,"n_cols":13,"rows_kept":45,"cut":false,"hidden":false,"merged":[]}
["Need a Sales Team and Region Report."]
[]
["Sales Rep","Sales","Region","Sales Team",null,"Sales Rep","Sales Team"]
["Kip",457,"North America",{"$f":"VLOOKUP(A4,$F$4:$G$19,2,FALSE)","v":"Sales Team 3"},null,"June","Sales Team 1"]
["June",368,"North America",{"$f":"VLOOKUP(A5,$F$4:$G$19,2,FALSE)","v":"Sales Team 1"},null,"Sioux","Sales Team 1",null,"Sum of Sales",null,"Region"]
["Gigi",725,"South America","Sales Team 2",null,"Poppi","Sales Team 1",null,"Sales Team","Sales Rep","North America","South America","Grand Total"]
["Mel",736,"South America","Sales Team 4",null,"Tyrone","Sales Team 1",null,"Sales Team 1",null,5301,4307,9608]
["Tyrone",606,"South America","Sales Team 1",null,"Gigi","Sales Team 2",null,"Sales Team 2",null,8162,5469,13631]
["Chin",1418,"South America","Sales Team 2",null,"Chin","Sales Team 2",null,"Sales Team 3",null,6211,2929,9140]
["Abdi",1110,"South America","Sales Team 3",null,"Sandi","Sales Team 2",null,"Sales Team 4",null,2499,2025,4524]
["Sandi",1489,"North America","Sales Team 2",null,"Shelia","Sales Team 2",null,"Grand Total",null,22173,14730,36903]
["June",175,"North America","Sales Team 1",null,"Fred","Sales Team 3"]
["Shelia",1354,"South America","Sales Team 2",null,"Kip","Sales Team 3"]
["Pham",132,"North America","Sales Team 3",null,"Abdi","Sales Team 3"]
["Gigi",449,"South America","Sales Team 2",null,"Pham","Sales Team 3"]
["Mo",1333,"North America","Sales Team 4",null,"Irene","Sales Team 4"]
["Sandi",1571,"North America","Sales Team 2",null,"Al","Sales Team 4"]
["Chin",1553,"North America","Sales Team 2",null,"Mel","Sales Team 4"]
["Tyrone",1196,"South America","Sales Team 1",null,"Mo","Sales Team 4"]
["June",679,"North America","Sales Team 1"]
["Pham",221,"South America","Sales Team 3"]
["Pham",1097,"North America","Sales Team 3"]
["Gigi",473,"North America","Sales Team 2"]
["Tyrone",573,"South America","Sales Team 1"]
["Gigi",1160,"South America","Sales Team 2"]
["June",1268,"North America","Sales Team 1"]
["Sandi",1635,"North America","Sales Team 2"]
["Mel",152,"North America","Sales Team 4"]
["Poppi",603,"North America","Sales Team 1"]
["Irene",458,"North America","Sales Team 4"]
["Sioux",741,"South America","Sales Team 1"]
["Fred",486,"North America","Sales Team 3"]
["Tyrone",1227,"North America","Sales Team 1"]
["Poppi",981,"North America","Sales Team 1"]
["Fred",798,"North America","Sales Team 3"]
["Sioux",1191,"South America","Sales Team 1"]
["Gigi",363,"South America","Sales Team 2"]
["Abdi",1332,"North America","Sales Team 3"]
["Mo",1289,"South America","Sales Team 4"]
["Fred",1625,"North America","Sales Team 3"]
["Pham",1261,"South America","Sales Team 3"]
["Mo",556,"North America","Sales Team 4"]
["Sandi",1441,"North America","Sales Team 2"]
["Fred",337,"South America","Sales Team 3"]
["Fred",284,"North America","Sales Team 3"]

{"sheet":"MikeQuery","index":5,"n_rows":45,"n_cols":11,"rows_kept":45,"cut":false,"hidden":false,"merged":[]}
["Need a Sales Team and Region Report."]
[]
["Sales Rep","Sales","Region",null,"Sales Rep","Sales Team",null,"Sales Team","Sales Rep","Region","Total Sales"]
["Kip",457,"North America",null,"June","Sales Team 1",null,"Sales Team 1","Tyrone","North America",1227]
["June",368,"North America",null,"Sioux","Sales Team 1",null,"Sales Team 1","Tyrone","South America",2375]
["Gigi",725,"South America",null,"Poppi","Sales Team 1",null,"Sales Team 1","Sioux","South America",1932]
["Mel",736,"South America",null,"Tyrone","Sales Team 1",null,"Sales Team 1","Poppi","North America",1584]
["Tyrone",606,"South America",null,"Gigi","Sales Team 2",null,"Sales Team 1","June","North America",2490]
["Chin",1418,"South America",null,"Chin","Sales Team 2",null,"Sales Team 2","Sandi","North America",6136]
["Abdi",1110,"South America",null,"Sandi","Sales Team 2",null,"Sales Team 2","Shelia","South America",1354]
["Sandi",1489,"North America",null,"Shelia","Sales Team 2",null,"Sales Team 2","Chin","North America",1553]
["June",175,"North America",null,"Fred","Sales Team 3",null,"Sales Team 2","Gigi","South America",2697]
["Shelia",1354,"South America",null,"Kip","Sales Team 3",null,"Sales Team 2","Chin","South America",1418]
["Pham",132,"North America",null,"Abdi","Sales Team 3",null,"Sales Team 2","Gigi","North America",473]
["Gigi",449,"South America",null,"Pham","Sales Team 3",null,"Sales Team 3","Abdi","North America",1332]
["Mo",1333,"North America",null,"Irene","Sales Team 4",null,"Sales Team 3","Fred","North America",3193]
["Sandi",1571,"North America",null,"Al","Sales Team 4",null,"Sales Team 3","Pham","North America",1229]
["Chin",1553,"North America",null,"Mel","Sales Team 4",null,"Sales Team 3","Abdi","South America",1110]
["Tyrone",1196,"South America",null,"Mo","Sales Team 4",null,"Sales Team 3","Kip","North America",457]
["June",679,"North America",null,null,null,null,"Sales Team 3","Pham","South America",1482]
["Pham",221,"South America",null,null,null,null,"Sales Team 3","Fred","South America",337]
["Pham",1097,"North America",null,null,null,null,"Sales Team 4","Mo","North America",1889]
["Gigi",473,"North America",null,null,null,null,"Sales Team 4","Mo","South America",1289]
["Tyrone",573,"South America",null,null,null,null,"Sales Team 4","Irene","North America",458]
["Gigi",1160,"South America",null,null,null,null,"Sales Team 4","Mel","North America",152]
["June",1268,"North America",null,null,null,null,"Sales Team 4","Mel","South America",736]
["Sandi",1635,"North America"]
["Mel",152,"North America"]
["Poppi",603,"North America"]
["Irene",458,"North America"]
["Sioux",741,"South America"]
["Fred",486,"North America"]
["Tyrone",1227,"North America"]
["Poppi",981,"North America"]
["Fred",798,"North America"]
["Sioux",1191,"South America"]
["Gigi",363,"South America"]
["Abdi",1332,"North America"]
["Mo",1289,"South America"]
["Fred",1625,"North America"]
["Pham",1261,"South America"]
["Mo",556,"North America"]
["Sandi",1441,"North America"]
["Fred",337,"South America"]
["Fred",284,"North America"]

{"sheet":"Sheet2","index":6,"n_rows":5,"n_cols":3,"rows_kept":5,"cut":false,"hidden":false,"merged":[]}
["Team","North America","South America"]
["Sales Team 1",3717,4307]
["Sales Team 2",9746,5469]
["Sales Team 3",6211,2929]
["Sales Team 4",2499,2025]

{"sheet":"BillPQ","index":7,"n_rows":45,"n_cols":6,"rows_kept":45,"cut":false,"hidden":false,"merged":[]}
["Need a Sales Team and Region Report."]
[]
["Sales Rep","Sales","Region",null,"Team","Sales Rep"]
["Kip",457,"North America",null,"Sales Team 1","June"]
["June",368,"North America",null,"Sales Team 1","Sioux"]
["Gigi",725,"South America",null,"Sales Team 2","Poppi"]
["Mel",736,"South America",null,"Sales Team 1","Tyrone"]
["Tyrone",606,"South America",null,"Sales Team 2","Gigi"]
["Chin",1418,"South America",null,"Sales Team 2","Chin"]
["Abdi",1110,"South America",null,"Sales Team 2","Sandi"]
["Sandi",1489,"North America",null,"Sales Team 2","Shelia"]
["June",175,"North America",null,"Sales Team 3","Fred"]
["Shelia",1354,"South America",null,"Sales Team 3","Kip"]
["Pham",132,"North America",null,"Sales Team 3","Abdi"]
["Gigi",449,"South America",null,"Sales Team 3","Pham"]
["Mo",1333,"North America",null,"Sales Team 4","Irene"]
["Sandi",1571,"North America",null,"Sales Team 4","Al"]
["Chin",1553,"North America",null,"Sales Team 4","Mel"]
["Tyrone",1196,"South America",null,"Sales Team 4","Mo"]
["June",679,"North America"]
["Pham",221,"South America"]
["Pham",1097,"North America"]
["Gigi",473,"North America"]
["Tyrone",573,"South America"]
["Gigi",1160,"South America"]
["June",1268,"North America"]
["Sandi",1635,"North America"]
["Mel",152,"North America"]
["Poppi",603,"North America"]
["Irene",458,"North America"]
["Sioux",741,"South America"]
["Fred",486,"North America"]
["Tyrone",1227,"North America"]
["Poppi",981,"North America"]
["Fred",798,"North America"]
["Sioux",1191,"South America"]
["Gigi",363,"South America"]
["Abdi",1332,"North America"]
["Mo",1289,"South America"]
["Fred",1625,"North America"]
["Pham",1261,"South America"]
["Mo",556,"North America"]
["Sandi",1441,"North America"]
["Fred",337,"South America"]
["Fred",284,"North America"]

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