SUMPRODUCT, MAX, Few Criteria

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
 
C

Chris26

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
 
N

NBVC

Try:

=INDEX(C1:C5000,MATCH(1,(A1:A5000=X1)*(B1:B5000=MAX(IF(A1:A5000=X1,B1:B5000))),0))

confirmed with CTRL+SHIFT+ENTER

or

=Lookup(2,1/((A1:A5000=X1)*(B1:B5000=Z1)),C1:C5000)


where Z1 contains the Sumproduct result from your post
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top