CODEWITHSUNDEEP
excelvba
Kevin
Kevin

Reputation: 31

How to find max value in a table but with different range?

Supposingly I have this table that I have pulled from the database:

Item number | Part number | Lead Time
   001      |     002     |   1.00
   001      |     005     |   5.00
   001      |     079     |   15.00
   002      |     078     |   10.00
   002      |     079     |   15.00
   003      |     004     |   30.00
   003      |     789     |   45.00

Are there any ways that I can add another column to find that maximum lead time of an item number? E.g:

Item number | Part number | Lead Time | Max Lead Time
   001      |     002     |   1.00    |     15.00
   001      |     005     |   5.00    |     15.00
   001      |     079     |   15.00   |     15.00
   002      |     078     |   10.00   |     10.00
   002      |     068     |   3.00    |     10.00
   003      |     004     |   30.00   |     45.00
   003      |     789     |   45.00   |     45.00

Upvotes: 0

Views: 47

Answers (1)

JNevill
JNevill

Reputation: 50283

As mentioned in the comments you can use =MAXIFS() to do this. That's a Max() function that is conditional. The condition here is that Column A values considered for the Max() match the current row's column A value:

=MAXIFS($C$2:$C$8, $A$2:$A$8, A2)

enter image description here

Upvotes: 2

Related Questions