excel 365 Sumif and Large

S

Sourdough123

Board Regular
Joined
Oct 25, 2021
Messages
142
Office Version
  1. 365
Platform
  1. Windows
Book1
BSBTBUBV
49CustomerSalesSqmRep
50Neil Laubcher94,107.0012.97Steffan
51Solid Ground24,691.126.92Tanja
52Spotlight132,649.8512.55Aretha
53SMM10,845.003.09Steffan
54Alto Kitchens3,248.231.82Tanja
55Alto Kitchens3,248.231.68Tanja
56Spotlight Joinery1,780.550.54Aretha
57Spotlight Joinery6,721.021.77Aretha
58Spotlight Joinery4,361.951.4Aretha
59Elk Stb3,469.000.85Aretha
60Sota Joinery 5,310.151.33Aretha
61Top Living24,292.088.55Tanja
62KITCHES 4 LIFE 3,046.180.96Aretha
63Elk 10,348.082.06Aretha
64Ellen Berry 2,584.801.33Tanja
65Solus Designs9,407.001.34Steffan
66Core Interiors20,897.002.54Steffan
67Craig Suanderson31,525.019.96Aretha
68STELBOU11,766.002.56Aretha
69Elk Tableview16,898.004.17Steffan
70Shahid12,848.003.89Steffan
71Brick N Board16,867.408.22Tanja
72MSP10,985.003.34Aretha
73Aristo50,664.0015.08Steffan
74Tara Decon 7,179.532.25Aretha
75My Kitchen Co8,027.882.18Aretha
76Echo Kitchens25,001.386.6Aretha
77Sota Joinery 7,934.512.48Aretha
78Elk Tableview10,840.000.76Steffan
79Top Living24,291.088.5Tanja
80Top Living15,440.135.94Tanja
81Top Living15,440.136.03Tanja
82MSP25,663.517.78Aretha
83Cami Interiors33,992.003.48Steffan
84MSM5,729.291Aretha
85Elk 14,254.633.69Aretha
86Craig Suanderson12,089.003.5Aretha
87Interior Fusion23,373.005.37Steffan
88Keith Vroom15,866.001.32STEFFAN
89MSP8,077.002.1Aretha
90MSP8,031.002.09Aretha
91MSP7,889.002.04ARETHA
92MSP7,909.002.05ARETHA
93Paxton Kitchens3,416.001.07ARETHA
94Brick N Board6,888.003.25Tanja
95Brick N Board6,888.003.25Tanja
96Brick N Board6,888.003.24Tanja
97Brick N Board6,888.003.34Tanja
98Corne Du Toit2,171.000.74STEFFAN
99Kurts Kitchens19,532.007.12Aretha
100Urban Projects5,538.001.43Steffan
101Urban Projects2,039.000.55STEFFAN
102Maruis Barhyssen29,954.003.08Tanja
103Craig Suanderson58,985.0019.06Aretha
104Brick N Board6,888.003.29Tanja
105Brick N Board6,888.003.34Tanja
106Brick N Board6,888.003.34Tanja
107Milyka37,543.6810.83Aretha
108Elk Paarl2,898.701.02Aretha
109Sota Joinery 11,853.112.83Aretha
110Black Wood Shopfitting 92,862.009.2Steffan
111Black Wood Shopfitting 24,589.006.99Steffan
112Ian Hardman 4,730.003.39Steffan
113Spotlight91,690.7118.45Aretha
114Milyka15,625.004.92Aretha
115Fine Craft Cpt13,327.403.19Aretha
116Crystal And Crumb35,919.008.24Steffan
117Spotlight34,236.004.44Aretha
118WD Construction 19,004.006.14Steffan
119Milyka63,348.7116.66Aretha
120Top Living15,440.135.99Aretha
121Top Living15,440.135.89Tanja
122Top Living22,044.838.5Tanja
123MSP24,324.008.49Aretha
124Elk Paarl8,154.002.7Aretha
125Elk Paarl8,154.000Aretha
126Designed By Louis30,709.499.49Aretha
127Edge Arch 60,599.449.9Aretha
128MSP9,617.303.14Aretha
129MSP9,679.443.16Aretha
130AE Kaste53,314.7312.33Aretha
131Brick N Board7,464.753.81Tanja
132Cami Interiors54,173.0014.55Steffan
133Black Wood Shopfitting 14,910.006.1Steffan
134Brick N Board7,464.753.81Tanja
135Brick N Board6,888.003.37Tanja
136Brick N Board6,888.003.37Tanja
137Revolution Shopfitters17,618.004.71Steffan
138MSP20,681.005.35Aretha
139AE Kaste1,528.000.16ARETHA
140R+N Master Builders1,990.000.93ARETHA
141Echo Kitchens6,476.002.03ARETHA
142Brick N Board5,997.003.39Tanja
143Brick N Board5,997.003.39TANJA
144Brick N Board5,997.003.38TANJA
145MSP11,701.003.14Aretha
146Brick N Board5,997.003.37Tanja
147Brick N Board6,888.003.39Tanja
148Brick N Board6,888.003.3Tanja
149Sublime Cupboards12,780.004.32Aretha
150Hannes Malan18,210.005.48Henk
151Interior Fusion18,092.005.69Steffan
152Brick N Board6,888.003.36Tanja
153Quantum Leap2,392.000.75ARETHA
154Euro Cassa109,579.0014.49Steffan
155Euro Cassa16,456.001.16Steffan
156MSM29,125.0013.32Aretha
157Libra9,418.001.86Steffan
158MSP4,005.001.16ARETHA
159Milyka55,624.0014.6Aretha
160Midcape Construction24,940.008.44Steffan
161Brick N Board22,821.006.22Tanja
162Core Interiors53,461.0010.32Steffan
163Edge Arch 6,261.002.879Aretha
164Elk SSW12,674.002.98Aretha
165Country Style24,140.0011.01Henk
166BRILLIANT HOME DESIGN10,825.002.97Aretha
167Interior Fusion7,306.002.02STEFFAN
168Brick N Board8,256.003.82Tanja
169Brick N Board12,635.005.63Tanja
170Conradie Kaste47,941.0010.92Steffan
171Aristo21,747.005.4Steffan
172Superior Cabinets115,509.0038.9Steffan
173Top Living24,291.008.88Tanja
174Top Living24,291.008.87Tanja
175CCMI9,399.002.15STEFFAN
176Brick N Board15,440.003.82Tanja
177Crontec10,574.002.92STEFFAN
178Spotlight15,165.001.47Aretha
179MSP5,448.001.66Aretha
180Spotlight68,604.0016.33Aretha
181Spotlight42,109.006.19Aretha
182Spotlight62,395.0015.6Aretha
183Spotlight86,118.004.9Aretha
184Big Ben3,806.001.73Tanja
185Big Ben3,825.001.74Tanja
186Big Ben5,366.002.445TANJA
187Big Ben4,925.002.24TANJA
188Kitch Me Up12,264.004.1Aretha
189Big Ben4,134.002.07Tanja
190Big Ben6,271.002.93Tanja
191Top Living6,938.002.53TANJA
192Dng Interiors30,405.0010.42Steffan
193Echo Kitchens2,137.000.61ARETHA
194My Kitchen Co4,266.001.06ARETHA
195VICTOR1,365.000.46ARETHA
196Crontec45,610.0017.656Steffan
197Brick N Board5,997.003.386Tanja
198Brick N Board5,325.003.51TANJA
199Colcab51,364.004.58HENK
200Colcab51,365.004.58HENK
201Brick N Board6,888.003.29Tanja
202Brick N Board7,699.003.78Tanja
203Crontec14,958.009.22Steffan
204Elk SSW24,373.006Aretha
205MSP23,700.006.53Aretha
206MSP18,780.004.89Aretha
207Elk Tableview1.004.73Steffan
208Top Living24,291.008.37Tanja
209Top Living15,440.005.97Tanja
210BENCHMARK16,692.001.14Steffan
211USED3.003.87Henk
212Interior Fusion44,726.009.39Steffan
213Elk Hermanus63,390.0010.56Aretha
214REGAL CONCEPTS10,236.002.93Steffan
215MSP19,300.005.05Aretha
216MSP24,324.006.39Aretha
217MSP12,103.003.18ARETHA
218Elk Hermanus7,539.002.2Aretha
219MSP9,116.002.54Aretha
220MSP11,170.003.14Aretha
221MSP11,253.003.16Aretha
222MSP11,220.003.15Aretha
223MSP7,704.001.98Aretha
224MSP7,644.001.98Aretha
225MSP9,645.002.63ARETHA
226Sota Joinery 20,719.004.91Aretha
227Designed By Louis20,107.006.38Aretha
228Brick N Board5,997.003.34Tanja
229Top Living15,440.005.99Tanja
230Top Living24,291.008.45Tanja
231Brick N Board5,997.003.3Tanja
232Aristo24,360.003.2Steffan
233Alto Kitchens5,820.002.97Tanja
234Brick N Board6,888.003.3Tanja
235Alto Kitchens5,820.002.99Tanja
236Brick N Board6,888.003.35Tanja
237Alto Kitchens5,820.003.01Tanja
238Alto Kitchens5,820.002.98Tanja
239Alto Kitchens5,820.002.99Tanja
240Brick N Board6,888.003.34Tanja
241Alto Kitchens5,820.002.97Tanja
242Alto Kitchens5,820.003.01Tanja
243CCMI41,871.007.57Steffan
244MSP11,247.003.26Aretha
245Cami Interiors85,466.0010.02Steffan
246CCMI9,407.002.7Steffan
247Brick N Board6,888.003.34Tanja
248Alto Kitchens5,820.002.99Tanja
249Alto Kitchens5,820.003.01Tanja
250Alto Kitchens5,820.002.98Tanja
251Alto Kitchens5,820.002.98Tanja
252Alto Kitchens5,820.002.99Tanja
253Designed By Louis19,298.002.24Aretha
254Designed By Louis10,783.002.66Aretha
255Brick N Board6,888.003.34Tanja
256Brick N Board6,888.003.32Tanja
257Brick N Board6,888.003.33Tanja
258Frans Prinsloo2,103.000.38Steffan
259MSP14,430.004.85Aretha
260MSP9,876.002.8Aretha
26176 Build95,163.0013.54Aretha
262Solid Ground18,842.005.82Tanja
263Top Living15,440.005.94Tanja
264NOORDHOEK HOTEL13,051.003.64Aretha
265MSP7,918.002.05Aretha
266MSP7,927.002.06Aretha
267MSP7,918.002.06ARETHA
268MSP7,930.002.04ARETHA
269Solid Ground8,670.002.71Tanja
270Big Ben5,360.002.44Tanja
271Big Ben5,340.002.43TANJA
272Big Ben4,616.002.44TANJA
273Alto Kitchens5,820.002.97Tanja
274Alto Kitchens5,820.002.99Tanja
275Big Ben4,621.002.44TANJA
276Big Ben4,616.002.44Tanja
277Big Ben4,621.002.44Tanja
278Elk Hermanus29,648.007.72Aretha
279Collaboration 37,800.004.89Henk
280OOOH INTERIORS39,919.006.63Aretha
281Spotlight11,081.000.58Aretha
282OOOH INTERIORS26,414.002.42Aretha
283Brick N Board6,888.003.4Tanja
284Revolution Shopfitters2,313.000.89STEFFAN
285Elk SSW67,829.007.46Aretha
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
BS50:BS285Cell Valuecontains "Conradie"textNO
BS50:BS110Cell Valuecontains "colcab"textNO
BT50:BT222Cell ValueduplicatestextNO

I want to extract top 10 customers in value ....the names
This is my formula but i need a sum function in to add up all duplicate customers values
=UNIQUE(XLOOKUP(LARGE(IF($BV$50:$BV$3500=BL$73,$BT$50:$BT$3500),SEQUENCE(10)),$BT$50:$BT$3500,$BS$50:$BS$3500))
Can you give hand please
Good Morning
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
How about
Fluff.xlsm
BCDEFGHI
1CustomerSalesSqmRep
2Neil Laubcher9410712.97SteffanSpotlight544048.6
3Solid Ground24691.126.92TanjaMSP387215.3
4Spotlight132649.912.55ArethaBrick N Board290599.9
5SMM108453.09SteffanTop Living282810.5
6Alto Kitchens3248.231.82TanjaCami Interiors173631
7Alto Kitchens3248.231.68TanjaMilyka172141.4
8Spotlight Joinery1780.550.54ArethaBlack Wood Shopfitting 132361
9Spotlight Joinery6721.021.77ArethaEuro Cassa126035
10Spotlight Joinery4361.951.4ArethaSuperior Cabinets115509
11Elk Stb34690.85ArethaElk SSW104876
12Sota Joinery 5310.151.33Aretha
13Top Living24292.088.55Tanja
14KITCHES 4 LIFE 3046.180.96Aretha
15Elk 10348.082.06Aretha
16Ellen Berry 2584.81.33Tanja
17Solus Designs94071.34Steffan
18Core Interiors208972.54Steffan
19Craig Suanderson31525.019.96Aretha
20STELBOU117662.56Aretha
21Elk Tableview168984.17Steffan
22Shahid128483.89Steffan
23Brick N Board16867.48.22Tanja
24MSP109853.34Aretha
25Aristo5066415.08Steffan
26Tara Decon 7179.532.25Aretha
27My Kitchen Co8027.882.18Aretha
28Echo Kitchens25001.386.6Aretha
29Sota Joinery 7934.512.48Aretha
30Elk Tableview108400.76Steffan
31Top Living24291.088.5Tanja
32Top Living15440.135.94Tanja
33Top Living15440.136.03Tanja
34MSP25663.517.78Aretha
35Cami Interiors339923.48Steffan
36MSM5729.291Aretha
37Elk 14254.633.69Aretha
38Craig Suanderson120893.5Aretha
39Interior Fusion233735.37Steffan
40Keith Vroom158661.32STEFFAN
41MSP80772.1Aretha
42MSP80312.09Aretha
43MSP78892.04ARETHA
44MSP79092.05ARETHA
45Paxton Kitchens34161.07ARETHA
46Brick N Board68883.25Tanja
47Brick N Board68883.25Tanja
48Brick N Board68883.24Tanja
49Brick N Board68883.34Tanja
50Corne Du Toit21710.74STEFFAN
51Kurts Kitchens195327.12Aretha
52Urban Projects55381.43Steffan
53Urban Projects20390.55STEFFAN
54Maruis Barhyssen299543.08Tanja
55Craig Suanderson5898519.06Aretha
56Brick N Board68883.29Tanja
57Brick N Board68883.34Tanja
58Brick N Board68883.34Tanja
59Milyka37543.6810.83Aretha
60Elk Paarl2898.71.02Aretha
61Sota Joinery 11853.112.83Aretha
62Black Wood Shopfitting 928629.2Steffan
63Black Wood Shopfitting 245896.99Steffan
64Ian Hardman 47303.39Steffan
65Spotlight91690.7118.45Aretha
66Milyka156254.92Aretha
67Fine Craft Cpt13327.43.19Aretha
68Crystal And Crumb359198.24Steffan
69Spotlight342364.44Aretha
70WD Construction 190046.14Steffan
71Milyka63348.7116.66Aretha
72Top Living15440.135.99Aretha
73Top Living15440.135.89Tanja
74Top Living22044.838.5Tanja
75MSP243248.49Aretha
76Elk Paarl81542.7Aretha
77Elk Paarl81540Aretha
78Designed By Louis30709.499.49Aretha
79Edge Arch 60599.449.9Aretha
80MSP9617.33.14Aretha
81MSP9679.443.16Aretha
82AE Kaste53314.7312.33Aretha
83Brick N Board7464.753.81Tanja
84Cami Interiors5417314.55Steffan
85Black Wood Shopfitting 149106.1Steffan
86Brick N Board7464.753.81Tanja
87Brick N Board68883.37Tanja
88Brick N Board68883.37Tanja
89Revolution Shopfitters176184.71Steffan
90MSP206815.35Aretha
91AE Kaste15280.16ARETHA
92R+N Master Builders19900.93ARETHA
93Echo Kitchens64762.03ARETHA
94Brick N Board59973.39Tanja
95Brick N Board59973.39TANJA
96Brick N Board59973.38TANJA
97MSP117013.14Aretha
98Brick N Board59973.37Tanja
99Brick N Board68883.39Tanja
100Brick N Board68883.3Tanja
101Sublime Cupboards127804.32Aretha
102Hannes Malan182105.48Henk
103Interior Fusion180925.69Steffan
104Brick N Board68883.36Tanja
105Quantum Leap23920.75ARETHA
106Euro Cassa10957914.49Steffan
107Euro Cassa164561.16Steffan
108MSM2912513.32Aretha
109Libra94181.86Steffan
110MSP40051.16ARETHA
111Milyka5562414.6Aretha
112Midcape Construction249408.44Steffan
113Brick N Board228216.22Tanja
114Core Interiors5346110.32Steffan
115Edge Arch 62612.879Aretha
116Elk SSW126742.98Aretha
117Country Style2414011.01Henk
118BRILLIANT HOME DESIGN108252.97Aretha
119Interior Fusion73062.02STEFFAN
120Brick N Board82563.82Tanja
121Brick N Board126355.63Tanja
122Conradie Kaste4794110.92Steffan
123Aristo217475.4Steffan
124Superior Cabinets11550938.9Steffan
125Top Living242918.88Tanja
126Top Living242918.87Tanja
127CCMI93992.15STEFFAN
128Brick N Board154403.82Tanja
129Crontec105742.92STEFFAN
130Spotlight151651.47Aretha
131MSP54481.66Aretha
132Spotlight6860416.33Aretha
133Spotlight421096.19Aretha
134Spotlight6239515.6Aretha
135Spotlight861184.9Aretha
136Big Ben38061.73Tanja
137Big Ben38251.74Tanja
138Big Ben53662.445TANJA
139Big Ben49252.24TANJA
140Kitch Me Up122644.1Aretha
141Big Ben41342.07Tanja
142Big Ben62712.93Tanja
143Top Living69382.53TANJA
144Dng Interiors3040510.42Steffan
145Echo Kitchens21370.61ARETHA
146My Kitchen Co42661.06ARETHA
147VICTOR13650.46ARETHA
148Crontec4561017.656Steffan
149Brick N Board59973.386Tanja
150Brick N Board53253.51TANJA
151Colcab513644.58HENK
152Colcab513654.58HENK
153Brick N Board68883.29Tanja
154Brick N Board76993.78Tanja
155Crontec149589.22Steffan
156Elk SSW243736Aretha
157MSP237006.53Aretha
158MSP187804.89Aretha
159Elk Tableview14.73Steffan
160Top Living242918.37Tanja
161Top Living154405.97Tanja
162BENCHMARK166921.14Steffan
163USED33.87Henk
164Interior Fusion447269.39Steffan
165Elk Hermanus6339010.56Aretha
166REGAL CONCEPTS102362.93Steffan
167MSP193005.05Aretha
168MSP243246.39Aretha
169MSP121033.18ARETHA
170Elk Hermanus75392.2Aretha
171MSP91162.54Aretha
172MSP111703.14Aretha
173MSP112533.16Aretha
174MSP112203.15Aretha
175MSP77041.98Aretha
176MSP76441.98Aretha
177MSP96452.63ARETHA
178Sota Joinery 207194.91Aretha
179Designed By Louis201076.38Aretha
180Brick N Board59973.34Tanja
181Top Living154405.99Tanja
182Top Living242918.45Tanja
183Brick N Board59973.3Tanja
184Aristo243603.2Steffan
185Alto Kitchens58202.97Tanja
186Brick N Board68883.3Tanja
187Alto Kitchens58202.99Tanja
188Brick N Board68883.35Tanja
189Alto Kitchens58203.01Tanja
190Alto Kitchens58202.98Tanja
191Alto Kitchens58202.99Tanja
192Brick N Board68883.34Tanja
193Alto Kitchens58202.97Tanja
194Alto Kitchens58203.01Tanja
195CCMI418717.57Steffan
196MSP112473.26Aretha
197Cami Interiors8546610.02Steffan
198CCMI94072.7Steffan
199Brick N Board68883.34Tanja
200Alto Kitchens58202.99Tanja
201Alto Kitchens58203.01Tanja
202Alto Kitchens58202.98Tanja
203Alto Kitchens58202.98Tanja
204Alto Kitchens58202.99Tanja
205Designed By Louis192982.24Aretha
206Designed By Louis107832.66Aretha
207Brick N Board68883.34Tanja
208Brick N Board68883.32Tanja
209Brick N Board68883.33Tanja
210Frans Prinsloo21030.38Steffan
211MSP144304.85Aretha
212MSP98762.8Aretha
21376 Build9516313.54Aretha
214Solid Ground188425.82Tanja
215Top Living154405.94Tanja
216NOORDHOEK HOTEL130513.64Aretha
217MSP79182.05Aretha
218MSP79272.06Aretha
219MSP79182.06ARETHA
220MSP79302.04ARETHA
221Solid Ground86702.71Tanja
222Big Ben53602.44Tanja
223Big Ben53402.43TANJA
224Big Ben46162.44TANJA
225Alto Kitchens58202.97Tanja
226Alto Kitchens58202.99Tanja
227Big Ben46212.44TANJA
228Big Ben46162.44Tanja
229Big Ben46212.44Tanja
230Elk Hermanus296487.72Aretha
231Collaboration 378004.89Henk
232OOOH INTERIORS399196.63Aretha
233Spotlight110810.58Aretha
234OOOH INTERIORS264142.42Aretha
235Brick N Board68883.4Tanja
236Revolution Shopfitters23130.89STEFFAN
237Elk SSW678297.46Aretha
Data
Cell Formulas
RangeFormula
H2:I11H2=LET(u,UNIQUE(FILTER(B2:B1000,B2:B1000<>"")),TAKE(SORT(HSTACK(u,SUMIFS(C:C,B:B,u)),2,-1),10))
Dynamic array formulas.
 
Upvote 0
Fluff said:
How about
Fluff.xlsm
BCDEFGHI
1CustomerSalesSqmRep
2Neil Laubcher9410712.97SteffanSpotlight544048.6
3Solid Ground24691.126.92TanjaMSP387215.3
4Spotlight132649.912.55ArethaBrick N Board290599.9
5SMM108453.09SteffanTop Living282810.5
6Alto Kitchens3248.231.82TanjaCami Interiors173631
7Alto Kitchens3248.231.68TanjaMilyka172141.4
8Spotlight Joinery1780.550.54ArethaBlack Wood Shopfitting 132361
9Spotlight Joinery6721.021.77ArethaEuro Cassa126035
10Spotlight Joinery4361.951.4ArethaSuperior Cabinets115509
11Elk Stb34690.85ArethaElk SSW104876
12Sota Joinery 5310.151.33Aretha
13Top Living24292.088.55Tanja
14KITCHES 4 LIFE 3046.180.96Aretha
15Elk 10348.082.06Aretha
16Ellen Berry 2584.81.33Tanja
17Solus Designs94071.34Steffan
18Core Interiors208972.54Steffan
19Craig Suanderson31525.019.96Aretha
20STELBOU117662.56Aretha
21Elk Tableview168984.17Steffan
22Shahid128483.89Steffan
23Brick N Board16867.48.22Tanja
24MSP109853.34Aretha
25Aristo5066415.08Steffan
26Tara Decon 7179.532.25Aretha
27My Kitchen Co8027.882.18Aretha
28Echo Kitchens25001.386.6Aretha
29Sota Joinery 7934.512.48Aretha
30Elk Tableview108400.76Steffan
31Top Living24291.088.5Tanja
32Top Living15440.135.94Tanja
33Top Living15440.136.03Tanja
34MSP25663.517.78Aretha
35Cami Interiors339923.48Steffan
36MSM5729.291Aretha
37Elk 14254.633.69Aretha
38Craig Suanderson120893.5Aretha
39Interior Fusion233735.37Steffan
40Keith Vroom158661.32STEFFAN
41MSP80772.1Aretha
42MSP80312.09Aretha
43MSP78892.04ARETHA
44MSP79092.05ARETHA
45Paxton Kitchens34161.07ARETHA
46Brick N Board68883.25Tanja
47Brick N Board68883.25Tanja
48Brick N Board68883.24Tanja
49Brick N Board68883.34Tanja
50Corne Du Toit21710.74STEFFAN
51Kurts Kitchens195327.12Aretha
52Urban Projects55381.43Steffan
53Urban Projects20390.55STEFFAN
54Maruis Barhyssen299543.08Tanja
55Craig Suanderson5898519.06Aretha
56Brick N Board68883.29Tanja
57Brick N Board68883.34Tanja
58Brick N Board68883.34Tanja
59Milyka37543.6810.83Aretha
60Elk Paarl2898.71.02Aretha
61Sota Joinery 11853.112.83Aretha
62Black Wood Shopfitting 928629.2Steffan
63Black Wood Shopfitting 245896.99Steffan
64Ian Hardman 47303.39Steffan
65Spotlight91690.7118.45Aretha
66Milyka156254.92Aretha
67Fine Craft Cpt13327.43.19Aretha
68Crystal And Crumb359198.24Steffan
69Spotlight342364.44Aretha
70WD Construction 190046.14Steffan
71Milyka63348.7116.66Aretha
72Top Living15440.135.99Aretha
73Top Living15440.135.89Tanja
74Top Living22044.838.5Tanja
75MSP243248.49Aretha
76Elk Paarl81542.7Aretha
77Elk Paarl81540Aretha
78Designed By Louis30709.499.49Aretha
79Edge Arch 60599.449.9Aretha
80MSP9617.33.14Aretha
81MSP9679.443.16Aretha
82AE Kaste53314.7312.33Aretha
83Brick N Board7464.753.81Tanja
84Cami Interiors5417314.55Steffan
85Black Wood Shopfitting 149106.1Steffan
86Brick N Board7464.753.81Tanja
87Brick N Board68883.37Tanja
88Brick N Board68883.37Tanja
89Revolution Shopfitters176184.71Steffan
90MSP206815.35Aretha
91AE Kaste15280.16ARETHA
92R+N Master Builders19900.93ARETHA
93Echo Kitchens64762.03ARETHA
94Brick N Board59973.39Tanja
95Brick N Board59973.39TANJA
96Brick N Board59973.38TANJA
97MSP117013.14Aretha
98Brick N Board59973.37Tanja
99Brick N Board68883.39Tanja
100Brick N Board68883.3Tanja
101Sublime Cupboards127804.32Aretha
102Hannes Malan182105.48Henk
103Interior Fusion180925.69Steffan
104Brick N Board68883.36Tanja
105Quantum Leap23920.75ARETHA
106Euro Cassa10957914.49Steffan
107Euro Cassa164561.16Steffan
108MSM2912513.32Aretha
109Libra94181.86Steffan
110MSP40051.16ARETHA
111Milyka5562414.6Aretha
112Midcape Construction249408.44Steffan
113Brick N Board228216.22Tanja
114Core Interiors5346110.32Steffan
115Edge Arch 62612.879Aretha
116Elk SSW126742.98Aretha
117Country Style2414011.01Henk
118BRILLIANT HOME DESIGN108252.97Aretha
119Interior Fusion73062.02STEFFAN
120Brick N Board82563.82Tanja
121Brick N Board126355.63Tanja
122Conradie Kaste4794110.92Steffan
123Aristo217475.4Steffan
124Superior Cabinets11550938.9Steffan
125Top Living242918.88Tanja
126Top Living242918.87Tanja
127CCMI93992.15STEFFAN
128Brick N Board154403.82Tanja
129Crontec105742.92STEFFAN
130Spotlight151651.47Aretha
131MSP54481.66Aretha
132Spotlight6860416.33Aretha
133Spotlight421096.19Aretha
134Spotlight6239515.6Aretha
135Spotlight861184.9Aretha
136Big Ben38061.73Tanja
137Big Ben38251.74Tanja
138Big Ben53662.445TANJA
139Big Ben49252.24TANJA
140Kitch Me Up122644.1Aretha
141Big Ben41342.07Tanja
142Big Ben62712.93Tanja
143Top Living69382.53TANJA
144Dng Interiors3040510.42Steffan
145Echo Kitchens21370.61ARETHA
146My Kitchen Co42661.06ARETHA
147VICTOR13650.46ARETHA
148Crontec4561017.656Steffan
149Brick N Board59973.386Tanja
150Brick N Board53253.51TANJA
151Colcab513644.58HENK
152Colcab513654.58HENK
153Brick N Board68883.29Tanja
154Brick N Board76993.78Tanja
155Crontec149589.22Steffan
156Elk SSW243736Aretha
157MSP237006.53Aretha
158MSP187804.89Aretha
159Elk Tableview14.73Steffan
160Top Living242918.37Tanja
161Top Living154405.97Tanja
162BENCHMARK166921.14Steffan
163USED33.87Henk
164Interior Fusion447269.39Steffan
165Elk Hermanus6339010.56Aretha
166REGAL CONCEPTS102362.93Steffan
167MSP193005.05Aretha
168MSP243246.39Aretha
169MSP121033.18ARETHA
170Elk Hermanus75392.2Aretha
171MSP91162.54Aretha
172MSP111703.14Aretha
173MSP112533.16Aretha
174MSP112203.15Aretha
175MSP77041.98Aretha
176MSP76441.98Aretha
177MSP96452.63ARETHA
178Sota Joinery 207194.91Aretha
179Designed By Louis201076.38Aretha
180Brick N Board59973.34Tanja
181Top Living154405.99Tanja
182Top Living242918.45Tanja
183Brick N Board59973.3Tanja
184Aristo243603.2Steffan
185Alto Kitchens58202.97Tanja
186Brick N Board68883.3Tanja
187Alto Kitchens58202.99Tanja
188Brick N Board68883.35Tanja
189Alto Kitchens58203.01Tanja
190Alto Kitchens58202.98Tanja
191Alto Kitchens58202.99Tanja
192Brick N Board68883.34Tanja
193Alto Kitchens58202.97Tanja
194Alto Kitchens58203.01Tanja
195CCMI418717.57Steffan
196MSP112473.26Aretha
197Cami Interiors8546610.02Steffan
198CCMI94072.7Steffan
199Brick N Board68883.34Tanja
200Alto Kitchens58202.99Tanja
201Alto Kitchens58203.01Tanja
202Alto Kitchens58202.98Tanja
203Alto Kitchens58202.98Tanja
204Alto Kitchens58202.99Tanja
205Designed By Louis192982.24Aretha
206Designed By Louis107832.66Aretha
207Brick N Board68883.34Tanja
208Brick N Board68883.32Tanja
209Brick N Board68883.33Tanja
210Frans Prinsloo21030.38Steffan
211MSP144304.85Aretha
212MSP98762.8Aretha
21376 Build9516313.54Aretha
214Solid Ground188425.82Tanja
215Top Living154405.94Tanja
216NOORDHOEK HOTEL130513.64Aretha
217MSP79182.05Aretha
218MSP79272.06Aretha
219MSP79182.06ARETHA
220MSP79302.04ARETHA
221Solid Ground86702.71Tanja
222Big Ben53602.44Tanja
223Big Ben53402.43TANJA
224Big Ben46162.44TANJA
225Alto Kitchens58202.97Tanja
226Alto Kitchens58202.99Tanja
227Big Ben46212.44TANJA
228Big Ben46162.44Tanja
229Big Ben46212.44Tanja
230Elk Hermanus296487.72Aretha
231Collaboration 378004.89Henk
232OOOH INTERIORS399196.63Aretha
233Spotlight110810.58Aretha
234OOOH INTERIORS264142.42Aretha
235Brick N Board68883.4Tanja
236Revolution Shopfitters23130.89STEFFAN
237Elk SSW678297.46Aretha
Data
Cell Formulas
RangeFormula
H2:I11H2=LET(u,UNIQUE(FILTER(B2:B1000,B2:B1000<>"")),TAKE(SORT(HSTACK(u,SUMIFS(C:C,B:B,u)),2,-1),10))
Dynamic array formulas.
Click to expand...
Thanks
BB
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
Morning
I ch
Book2
CDEFGHIJKL
5ArethaTanjaSteffanTanjaHenk
6Elk SSW51,721.00Top Living38,498.00Superior Cabinets157,625.00Top Living38,498.00OUHOUT76,000.00
7 MSP 47,436.00ALTO KITCHENS25,492.00Crontec42,669.00ALTO KITCHENS25,492.00GLENSON-
8 My Kitchen Co 40,881.00BIG BEN4,634.00D 5 Artisan 15,788.00BIG BEN4,634.00Superior Cabinets-
9 Sota Joinery 15,470.00GLENSON-GLENSON13,211.00GLENSON-Elk SSW-
10 ECHO KITCHENS 11,328.00OUHOUT-COENIE MARAIS4,337.00OUHOUT-Top Living-
11 BTM JOINERY 10,320.00Superior Cabinets-FIONA STUART3,303.00Superior Cabinets-My Kitchen Co-
12 LISA 5,516.00Elk SSW-OUHOUT-Elk SSW-BTM JOINERY-
13 GLENSON -My Kitchen Co-Elk SSW-My Kitchen Co-COENIE MARAIS-
14 OUHOUT -BTM JOINERY-Top Living-BTM JOINERY-D 5 Artisan -
15 Superior Cabinets -COENIE MARAIS-My Kitchen Co-COENIE MARAIS-ECHO KITCHENS-
16182,672.0068,624.00236,933.0068,624.0076,000.00
Sheet1
ange formula to extract date for each rep but do not want names where values are Zero
=LET(u,UNIQUE(FILTER($CH$50:$CH$1000,$CH$50:$CH$1000<>"")),TAKE(SORT(HSTACK(u,SUMIFS($CJ:$CJ,$CH:$CH,u,$CI:$CI,CO49)),2,-1),10))
Thanks
BB
 
Upvote 0
How about
Excel Formula: 
=LET(u,UNIQUE(FILTER($CH$50:$CH$1000,$CH$50:$CH$1000<>"")),s,SUMIFS($CJ:$CJ,$CH:$CH,u,$CI:$CI,CO49),TAKE(SORT(FILTER(HSTACK(u,s),s<>0),2,-1),10))
 
Upvote 0
Solution
Fluff said:
How about
Excel Formula: 
=LET(u,UNIQUE(FILTER($CH$50:$CH$1000,$CH$50:$CH$1000<>"")),s,SUMIFS($CJ:$CJ,$CH:$CH,u,$CI:$CI,CO49),TAKE(SORT(FILTER(HSTACK(u,s),s<>0),2,-1),10))
Click to expand...
100%
Thank you
BB
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Index match with multiple criteria, sheets & columns
2
Replies
10
Views
734
Fluff
Fluff
D
  • Solved
Lookup/Vlookup/Hlookup/Index & Match? Need help
2
Replies
19
Views
1K
DarkSmile
D
H
  • Question Question
How do I create a table that then considers whether or not clients appear in other tables?
Replies
0
Views
417
HelpPlease123
H
D
  • Question Question
String running first time but when looping back it isnt placing correctly.
Replies
1
Views
506
DanSMT
D
D
  • Question Question
VBA Loop to create Excel Sheet, save excel sheet to specific folder on computer and generate Outlook 365 Email with attached excel sheet.
Replies
1
Views
579
Fluff
Fluff

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top