Hi Biff, doesn't seem to be working. The rows are pretty long (up to 423) in
my actual worksheet, and i noticed that some cells are blank. Does this pose
a problem?

"T. Valko" wrote:

b) show which pairing that resulted the above (i.e. 12 and 11) within a
cell.

Try this array formula** :

=INDEX(B1:B10&"-"&C1:C10,MODE(MATCH(B1:B10&"-"&C1:C10,B1:B10&"-"&C1:C10,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

This will return the result in the form 12-11. Note that there *must* be at
least 2 instances of matching pairs for this to work. Otherwise the formula
will return an error.

a) show the total of the most frequent occurence (which is 4 per above)

This formula is based on the result of the above formula. Assume the above
formula is entered in cell E1.

=SUMPRODUCT(--(B1:B10&"-"&C1:C10=E1))


--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
Thanks. Another question on the same table, but expanded a bit:
Nuts 21 8 Pins
Bolts 11 13 Screws
Screws 12 11 Nuts
Pins 6 14 Bolts
Caps 4 19 Nuts
Screws 12 11 Nuts
Pins 8 12 Bolts
Caps 4 19 Screws
Bolts 12 11 Nuts
Screws 12 11 Pins

How do i use check for column B and C for the most frequent occurence of
pairing? Table above shows 12and11 occurring 4 times. I want to:
a) show the total of the most frequent occurence (which is 4 per above);
b) show which pairing that resulted the above (i.e. 12 and 11) within a
cell.

"T. Valko" wrote:

Try this array formula** :

=INDEX(B$1:C$5,MATCH(MAX(B$1:B$5+C$1:C$5),B$1:B$5+ C$1:C$5,0),ROWS(F$2:F2))

Copy down to F3

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
Here's my table:

A B C D
Nuts 21 8 Pins
Bolts 11 13 Screws
Screws 12 11 Nuts
Pins 6 14 Bolts
Caps 4 19 Nuts

I'm using =MAX(B1:B5)+(C1:C5) to get the max value returned on cell F1.
What
i need is to be able to display the following:
cell F2 to show the value from column B of the MAX combination
cell F3 to show the value from column C of the MAX combination

From example above, MAX should return 29 from row 1. How do i then
display
B1 and C1 (in different cells) that adds up to the MAX value? Thanks in
advance for any assistance!