Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find MAX number with criteria
Hello all:
Using Excel 2003 I have two lists, column A contains a list of names that have reoccurrences of the same name many times within the list and I’ve already made a list in column E of those unique names. Column B is a list of numbers in random order and no two numbers are the same. My problem is trying to make a new list in column F by finding the MAX number in column B that is associated with the persons name in column E. I’ve tried many different formulas with no success, can any one please help me out with this. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find MAX number with criteria
Try this array formula** :
=MAX(IF(A$1:A$100=E1,B$1:B$100)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down as needed. -- Biff Microsoft Excel MVP "GoBow777" wrote in message ... Hello all: Using Excel 2003 I have two lists, column A contains a list of names that have reoccurrences of the same name many times within the list and I've already made a list in column E of those unique names. Column B is a list of numbers in random order and no two numbers are the same. My problem is trying to make a new list in column F by finding the MAX number in column B that is associated with the persons name in column E. I've tried many different formulas with no success, can any one please help me out with this. -- GoBow777 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find MAX number with criteria
Let's say the names are in A2:A140, the numbers in B2:B140
Let's say the first name in E is in E2 In F2 enter =MAX(IF($A$2:$A$140=E2,$B$2:$B$140)) But since this is an array formula commit it with CTRL+SHIFT+ENTER not just ENTER The formula bar will display {=MAX(IF($A$2:$A$140=E2,$B$2:$B$140))} but Excel not you has added the braces Copy F2 down the column Do NOT try to use full columns references =MAX(IF(A:A=E2,B:B)) unless you are working in Excel 2007 best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "GoBow777" wrote in message ... Hello all: Using Excel 2003 I have two lists, column A contains a list of names that have reoccurrences of the same name many times within the list and I’ve already made a list in column E of those unique names. Column B is a list of numbers in random order and no two numbers are the same. My problem is trying to make a new list in column F by finding the MAX number in column B that is associated with the persons name in column E. I’ve tried many different formulas with no success, can any one please help me out with this. -- GoBow777 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find MAX number with criteria
Try one of these:
=MAX(INDEX((A1:A100=E1)*B1:B100,)) or =SUMPRODUCT(MAX((A1:A100=E1)*B1:B100)) just press ENTER "GoBow777" wrote: Hello all: Using Excel 2003 I have two lists, column A contains a list of names that have reoccurrences of the same name many times within the list and Ive already made a list in column E of those unique names. Column B is a list of numbers in random order and no two numbers are the same. My problem is trying to make a new list in column F by finding the MAX number in column B that is associated with the persons name in column E. Ive tried many different formulas with no success, can any one please help me out with this. -- GoBow777 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find MAX number with criteria
Hello,
I suggest to use my UDF Mfreq: http://www.sulprobil.com/html/mfreq.html Please note that this is an array function. Select your desired output area and then enter the function with all necessary parameters and finish with CTRL + SHIFT + ENTER, not only ENTER. Regards, Bernd |
#6
|
|||
|
|||
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find a column number using variable criteria? | Excel Worksheet Functions | |||
Find previous number and find next number in column | Excel Discussion (Misc queries) | |||
EXCEL - Meet 2 criteria, then find next case of third criteria | Excel Worksheet Functions | |||
Countif using format criteria not number criteria? | Excel Worksheet Functions | |||
Countif using format criteria....not number criteria? | Excel Worksheet Functions |