Need next number

  • Thread starter Thread starter Nadine
  • Start date Start date
N

Nadine

I have the following information
Col A ColB ColC ColD Col E (where the formula needs to go
but showing what the result should be)
CA1234 75016 AA 1 5
CA1234 75016 AA 1 5
CA1234 75016 AA 1 5
CA1234 75016 AA 2 5
CA5678 75321 BB 4 4
CA5678 75321 BB 3 4
CA1234 75016 AA 5 5
CA5678 75321 CD 1 4
CA5678 75321 CD 4 4
CA5678 75321 BB 1 4
Col E should find the largest number in Col D that belongs to the
combination in the first 3 columns.
Any ideas on the formula to go in each line in col E?
Thank you.
 
As responded to your multi-post in .worksheet.functions ...

Array-enter* into E1, copy down:
=MAX(IF((A$1:A$10=A1)*(B$1:B$10=B1)*(C$1:C$10=C1),D$1:D$10))
*press CTRL+SHIFT+ENTER to confirm the formula
 
Try in E1
=SUMPRODUCT(MAX((A1:A10=A1)*(B1:B10=B1)*(C1:C10=C1)*(D1:D10)))
and copy down
But you have to make sure column D are numbers and not text stored as
numbers.

Regards
Steve
 
Try in E1
=SUMPRODUCT(MAX((A1:A10=A1)*(B1:B10=B1)*(C1:C10=C1)*(D1:D10)))
and copy down
But you have to make sure column D are numbers and not text stored as
numbers.

Regards
Click to expand...

Dear All,

In Steve's formula we need to freez the range - as
Try in E1
=SUMPRODUCT(MAX(($A$1:$A$10=A1)*($B$1:$B$10=B1)*($C$1:$C$10=C1)*($D
$1:$D$10)))
and copy down
But you have to make sure column D are numbers and not text stored as
numbers.
 
Back
Top