SUMPRODUCT, MAX, Few Criteria

  • Thread starter Thread starter Chris26
  • Start date Start date
C

Chris26

I have imported lots of data into Excel, i’ve inc a small example. I need a
bit of help extracting some of the info into a table.

Col A(noderef) Col B (level) Col C (Text Format)
Node1 75 Oct1993-Oct1994
Node1 76 Oct1994-Oct1995
Node1 79 Oct1995-Oct1996
Node1 74 Oct1996-Oct1997
Node2
Node999etc

I have used the formula
SUMPRODUCT(MAX((A1:A5000=X1)*B1:B5000)) to give me the highest value of Col
B When Ref in Col A = Ref in Col X (my table).

I also want Col Y (my table) to give the show the text from Col C that
corresponds to the formula I used above. I.e. For above example

Col X = Node1, Col Y = 77.35, Col X = “Oct1995 to Oct 1996â€.

I have tried a few diff things but can’t get anything to work. Sorry if Q a
bit long !
Thanks In advance
Chris
 
Chris26 said:
I have imported lots of data into Excel, i’ve inc a small example. I need a
bit of help extracting some of the info into a table.

Col A(noderef) Col B (level) Col C (Text Format)
Node1 75 Oct1993-Oct1994
Node1 76 Oct1994-Oct1995
Node1 79 Oct1995-Oct1996
Node1 74 Oct1996-Oct1997
Node2
Node999etc

I have used the formula
SUMPRODUCT(MAX((A1:A5000=X1)*B1:B5000)) to give me the highest value of Col
B When Ref in Col A = Ref in Col X (my table).

I also want Col Y (my table) to give the show the text from Col C that
corresponds to the formula I used above. I.e. For above example

Col X = Node1, Col Y = 79, Col X = “Oct1995 to Oct 1996â€.

I have tried a few diff things but can’t get anything to work. Sorry if Q a
bit long !
Thanks In advance
Chris
Click to expand...
 
Back
Top