Find Largest Value (then associate) via Multiple Sums

[B1313]

Hello,

So, I have a relatively simple request. I have the following table.

Sizes:	Red	Blue	Green	Yellow	Black	White	Purple	Orange	Brown
Total	5	1	16	15	21	12	7	9	1
Item 1	2	0	1	8	8	1	1	9	0
Item 2	0	0	0	0	1	0	0	0	0
Item 3	0	0	0	0	0	0	0	0	0
Item 4	1	1	14	7	12	11	6	0	1
Item 5	0	0	1	0	0	0	0	0	0
Item 6	2	0	0	0	0	0	0	0	0
Item 7	0	0	0	0	0	0	0	0	0
Other	0	0	0	0	0	0	0	0	0

<tbody>
</tbody>

How would I do a comparison of the following summations: B3:J3, B4:J4, B5:J5, etc and find the largest then tell which Item number that is? For example, Item 4 is the biggest here and that is what the formula should return.

Also, do the same thing consequentially for the vertical columns (which just uses the total row in theory) and which ever is the biggest return the color that corresponds. For example, Green in this case.

Both will be separate cell formulas (ie the vertical comparisons will have their own cell and the horizontal ones their own cell).

Sample Workbook: https://www.dropbox.com/s/8wispz7r6bl0178/Sample.xlsx?dl=0

Thanks

 

[FDibbins]

Seeing as how you already have a (helper) row for the column totals, why not just add another 1 for the row totals?

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	Sizes:	Red	Blue	Green	Yellow	Black	White	Purple	Orange	Brown
2​	Total	5	1	16	15	21	12	7	9	1
3​	Item 1	2	0	1	8	8	1	1	9	0	30​
4​	Item 2	0	0	0	0	1	0	0	0	0	1​
5​	Item 3	0	0	0	0	0	0	0	0	0	0​
6​	Item 4	1	1	14	7	12	11	6	0	1	53​
7​	Item 5	0	0	1	0	0	0	0	0	0	1​
8​	Item 6	2	0	0	0	0	0	0	0	0	2​
9​	Item 7	0	0	0	0	0	0	0	0	0	0​
10​	Other	0	0	0	0	0	0	0	0	0	0​
11​
12​		Black
13​		Item 4

B12=INDEX($B$1:$J$1,MATCH(LARGE($B$2:$J$2,1),$B$2:$J$2,0))
B13=INDEX($A$3:$A$10,MATCH(LARGE($K$3:$K$10,1),$K$3:$K$10,0))

 

[B1313]

Thanks. I would add one, but the table I gave you is just a small fraction of the total table. The table keeps extending beyond the last color as the next set uses the same items and colors but different data results and my boss is OCD about the structure staying the same (yeah, he is one of those). Is there a way sum them up individually and then do the index match?

 

[FDibbins]

So the totals in the headings is still OK?
Can you insert a column (uyou can hide it, if needed), and put the totals in there? The SUM() range can be as wide as you want it

 

[B1313]

Yeah, heading totals are fine (he put them there), the side though he doesn't like, but I will see what I can do with hiding a column idea. Just in case, is there any other way to do it?

 

[FDibbins]

no short way that I can think of

 

[Aladin Akyurek]

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

1​

Sizes:

Red

Blue

Green

Yellow

Black

White

Purple

Orange

Brown

max

max

2​

Total

5​

1​

16​

15​

74​

12​

7​

9​

1​

74​

53​

3​

Item 1

2​

0​

1​

8​

8​

1​

1​

9​

0​

count

count

4​

Item 2

0​

0​

0​

0​

1​

0​

0​

0​

0​

1​

2​

5​

Item 3

0​

0​

0​

0​

0​

0​

0​

0​

0​

color(s)

item(s)

6​

Item 4

1​

1​

14​

7​

12​

11​

6​

0​

1​

Black

Item 4

7​

Item 5

0​

0​

1​

0​

0​

0​

0​

0​

0​

Other

8​

Item 6

2​

0​

0​

0​

0​

0​

0​

0​

0​

9​

Item 7

0​

0​

0​

0​

0​

0​

0​

0​

0​

10​

Other

0​

0​

0​

0​

53​

0​

0​

0​

0​

In M2 enter:

=MAX(B2:J2)

In M4 enter:

=COUNTIFS(B2:J2,M2)

In M6 control+shift+enter, not just enter, and copy down:

=IF(ROWS($M$6:M6)<=$M$4,INDEX($B$1:$J$1,SMALL(IF($B$2:$J$2=$M$2,
    COLUMN($B$1:$J$1)-COLUMN($B$1)+1),ROWS($M$6:M6))),"")<strike></strike>

In O2 control+shift+enter, not just enter:

=MAX(MMULT(B3:J10,TRANSPOSE(COLUMN(B3:J10)^0)))<strike></strike>

In O4 control+shift+enter, not just enter:

=SUM(IF(MMULT(B3:J10,TRANSPOSE(COLUMN(B3:J10)^0))=O2,1))

In O6 control+shift+enter, not just enter, and copy down:

=IF(ROWS($O$6:O6)<=$O$4,INDEX($A$3:$A$10,
    SMALL(IF(MMULT($B$3:$J$10,TRANSPOSE(COLUMN($B$3:$J$10)^0))=$O$2,
    ROW($A$3:$A$10)-ROW($A$3)+1),ROWS($O$6:O6))),"")<strike></strike>

 

[B1313]

~snip~

Impressive solution. I ended up doing the dirty "hide the totals column" method, but your method intrigues me because it handles various overlaps such as too items having the highest amount or colors. How does it handle data if all of it is zero? It would be a simple if statement to control it down the line somewhere, but I am just curious. Could you upload a sample sheet for me to test some data on?

 

[Aladin Akyurek]

Impressive solution. I ended up doing the dirty "hide the totals column" method, but your method intrigues me because it handles various overlaps such as too items having the highest amount or colors. How does it handle data if all of it is zero? It would be a simple if statement to control it down the line somewhere, but I am just curious. Could you upload a sample sheet for me to test some data on?

You can rebuild the set up in your workbook.

If you are worried about empty data cells, you need a small modification of the formulas I already offered...

O2 >>

{=MAX(MMULT(IF(ISNUMBER(B3:J10),B3:J10,0),TRANSPOSE(COLUMN(B3:J10)^0)))}

O4 >>

{=SUM(IF(MMULT(IF(ISNUMBER(B3:J10),B3:J10,0),TRANSPOSE(COLUMN(B3:J10)^0))=O2,1))}

O6 >>

{=IF(ROWS($O$6:O6)<=$O$4,INDEX($A$3:$A$10,SMALL(IF(MMULT(IF(ISNUMBER($B$3:$J$10),$B$3:$J$10,0),TRANSPOSE(COLUMN($B$3:$J$10)^0))=$O$2,ROW($A$3:$A$10)-ROW($A$3)+1),ROWS($O$6:O6))),"")}