Excel Problem

[AMS]

Found your web while browsing for help with an Excel formula, or combination that I was trying to make work. Maybe you can help me, maybe you can’t. Let’s say I need cell C38 to pick from range C10:C30 all values of “5”. When it finds all these “5” values, I need it to pick the highest value in cell range D10:D30 that are in same row as the C column “5” value, even if more than one are equal values, and put this value in cell C38. I’ve been trying to combine LARGE, SUMIF,COUNTIF formulas, but cannot get the right syntax or combination. Is this some kind of nested function? I am not too advanced on Excel, but am learning. I appreciate any help you can provide.

[Aladin Akyurek]

On 2002-04-10 12:15, AMS wrote:
Found your web while browsing for help with an Excel formula, or combination that I was trying to make work. Maybe you can help me, maybe you can’t. Let’s say I need cell C38 to pick from range C10:C30 all values of “5”. When it finds all these “5” values, I need it to pick the highest value in cell range D10:D30 that are in same row as the C column “5” value, even if more than one are equal values, and put this value in cell C38. I’ve been trying to combine LARGE, SUMIF,COUNTIF formulas, but cannot get the right syntax or combination. Is this some kind of nested function? I am not too advanced on Excel, but am learning. I appreciate any help you can provide.

In C38 enter:

=SUMPRODUCT(MAX((C10:C30=5)*(D10:D30)))

Is this what you're looking for?

[Dave Patton]

Hello AMS

You can secure the amount with an Array Formula. You MUST enter an array formula with Ctrl-Shift-Enter (CSE) instead of just Enter and you must also use CSE if you edit the formula.

=MAX(IF(C10:C30=5,(D10:D30)))

or use equivalent which does not require array entry

=SUMPRODUCT(MAX((C10:C30=5)*(D10:D30)))



[ This Message was edited by: Dave Patton on 2002-04-10 12:32 ]

[AMS]

The SUMPRODUCT command worked great. Obrigado. Now, if I wanted to keep the original formula and add J10:J30 to find the "5" value and I10:I30 for the largest value next to the J cell with a "5" and pick the largest value from Col D & I next to the cell with a "5", how would that be written?

[Aladin Akyurek]

On 2002-04-10 12:50, AMS wrote:
The SUMPRODUCT command worked great. Obrigado. Now, if I wanted to keep the original formula and add J10:J30 to find the "5" value and I10:I30 for the largest value next to the J cell with a "5" and pick the largest value from Col D & I next to the cell with a "5", how would that be written?

Not sure I understood the request, but it looks like:

=MAX(SUMPRODUCT(MAX((C10:C30=5)*(D10:D30))),SUMPRODUCT(MAX((J10:J30=5)*(I10:I30))))

Right?

[AMS]

Aladin--that did it. Thanks a bunch.

AMS