Complicated sorting and summing if

[MFGdatahound]

Hello geniuses,

Imagine the table below is longer with more variables. Each color is a company. Number is dollars earned. There are three different businesses: A, B, and C. I'm trying to take the full sum of each company's book of business, but classify them by the more dominant business. So if Yellow made $8, 3$ of that was through business B, but the more profitable was business A. The result would output "A" because it was the stronger business. I have no idea how to do it, and there are too many companies to filter each field.

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Company
[/TD]
[TD="class: xl65, width: 64"]$
[/TD]
[TD="class: xl65, width: 64"]Business
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Yellow[/TD]
[TD="class: xl65, width: 64"]5[/TD]
[TD="class: xl65, width: 64"]A[/TD]
[/TR]
[TR]
[TD="class: xl65"]Yellow[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]B[/TD]
[/TR]
[TR]
[TD="class: xl65"]Orange[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]C[/TD]
[/TR]
[TR]
[TD="class: xl65"]Blue[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]C[/TD]
[/TR]
[TR]
[TD="class: xl65"]Blue[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]B[/TD]
[/TR]
[TR]
[TD="class: xl65"]Blue[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]B[/TD]
[/TR]
[TR]
[TD="class: xl65"]Green[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]A[/TD]
[/TR]
[TR]
[TD="class: xl65"]Green[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]B[/TD]
[/TR]
[TR]
[TD="class: xl65"]Red[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]A[/TD]
[/TR]
[TR]
[TD="class: xl65"]Red[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]A[/TD]
[/TR]
[TR]
[TD="class: xl65"]Red[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]A[/TD]
[/TR]
[TR]
[TD="class: xl65"]Red[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]B[/TD]
[/TR]
[TR]
[TD="class: xl65"]Red[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]C[/TD]
[/TR]
</tbody>[/TABLE]


Appreciate any advice,
Thanks.

 

[Latchmaker]

Use a Pivot Table here is what your data above looks like.
<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th></th><th> </th><th>Sum of $</th></tr>
<tr><td>Blue</td><td>B</td><td>9</td></tr>
<tr><td> </td><td>C</td><td>8</td></tr>
<tr><td>Green</td><td>B</td><td>8</td></tr>
<tr><td> </td><td>A</td><td>2</td></tr>
<tr><td>Orange</td><td>C</td><td>1</td></tr>
<tr><td>Red</td><td>A</td><td>14</td></tr>
<tr><td> </td><td>B</td><td>9</td></tr>
<tr><td> </td><td>C</td><td>5</td></tr>
<tr><td>Yellow</td><td>A</td><td>5</td></tr>
<tr><td> </td><td>B</td><td>3</td></tr>
<tr><td>Grand Total</td><td> </td><td>64</td></tr>
</table>

 

[MFGdatahound]

I was including pivot tables in the term "filter". I have too many "colors" to go through each and pick out the best "letters" manually. I need some type of process to do that for me. This might not be a thing. LOL. My database was not made intuitively.

 

[AlanY]

try this, copy into E2 to G2 and down
adjust the ranges to suit

Excel 2012
	A	B	C	D	E	F	G
1	Company	$	Business		Company	$	Business
2	Yellow	5	A		Yellow	5	A
3	Yellow	3	B		Orange	1	C
4	Orange	1	C		Blue	8	C
5	Blue	8	C		Green	8	B
6	Blue	1	B		Red	9	A
7	Blue	8	B
8	Green	2	A
9	Green	8	B
10	Red	2	A
11	Red	3	A
12	Red	9	A
13	Red	9	B
14	Red	5	C
Sheet2

Excel 2012
	E	F	G
2	Yellow	5	A
Sheet2

Cell Formulas
Range	Formula
F2	=SUMPRODUCT(LARGE(($A$2:$A$14=$E2)*($B$2:$B$14),1))
E2	{=INDEX($A$2:$A$14, MATCH(0, COUNTIF($E$1:E1, $A$2:$A$14), 0))}
G2	{=INDEX($C$2:$C$14,MATCH(E2&F2,$A$2:$A$14&$B$2:$B$14,0),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.