Excel Problem

AMS

New Member
Joined
Apr 9, 2002
Messages
3
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top