Home |
Search |
Today's Posts |
#1
|
|||
|
|||
max and lookup
I have 2 columns of data; my item number and amount of quantity reserved. I
want to be able to get the max of a quantity reserved by looking up the item number. For example, I want to find out the max on Quantity for Item #100 = 10. Item # Quantity 101 5 100 10 102 4 100 8 102 9 101 11 |
#2
|
|||
|
|||
max and lookup
Hi
Try one of these: =SUMPRODUCT(MAX((A2:A7=100)*(B2:B7))) OR.... Array entered using the key combo of VTRL,SHIFT,ENTER: =MAX(IF(A2:A7=100,B2:B7)) Biff "eoht" wrote in message ... I have 2 columns of data; my item number and amount of quantity reserved. I want to be able to get the max of a quantity reserved by looking up the item number. For example, I want to find out the max on Quantity for Item #100 = 10. Item # Quantity 101 5 100 10 102 4 100 8 102 9 101 11 |
#3
|
|||
|
|||
max and lookup
Ooops!
Array entered using the key combo of VTRL,SHIFT,ENTER: Should be: Array entered using the key combo of CTRL,SHIFT,ENTER: Biff "Biff" wrote in message ... Hi Try one of these: =SUMPRODUCT(MAX((A2:A7=100)*(B2:B7))) OR.... Array entered using the key combo of VTRL,SHIFT,ENTER: =MAX(IF(A2:A7=100,B2:B7)) Biff "eoht" wrote in message ... I have 2 columns of data; my item number and amount of quantity reserved. I want to be able to get the max of a quantity reserved by looking up the item number. For example, I want to find out the max on Quantity for Item #100 = 10. Item # Quantity 101 5 100 10 102 4 100 8 102 9 101 11 |
#4
|
|||
|
|||
max and lookup
This is an array formula, meaning you enter it with Shift+Ctrl+Enter
=MAX(IF(A1:A6=D1,B1:B6,0)) where the Item #s are in A1:A6 and the item# you're interested in is in D1 "eoht" wrote: I have 2 columns of data; my item number and amount of quantity reserved. I want to be able to get the max of a quantity reserved by looking up the item number. For example, I want to find out the max on Quantity for Item #100 = 10. Item # Quantity 101 5 100 10 102 4 100 8 102 9 101 11 |
#5
|
|||
|
|||
max and lookup
If your data in is cells A1:B11, try this:
c1: =SUMPRODUCT(MAX(--(A2:A10=100)*(B2:B10))) Does that help? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "eoht" wrote: I have 2 columns of data; my item number and amount of quantity reserved. I want to be able to get the max of a quantity reserved by looking up the item number. For example, I want to find out the max on Quantity for Item #100 = 10. Item # Quantity 101 5 100 10 102 4 100 8 102 9 101 11 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|