Selecting a range of data

[grimey21]

Looking for some help to select a range of data that will give me the maximum sum of one field while taking into consideration the maximum limit of 2 other fields. Can someone help me take this on?

[kev_]

Welcome to the forum

Please attach a small sample Excel workbook -not a picture
- max 50 rows of normal data plus expected results (in separate sheet perhaps)
- good sample data = best solution
- remove confidential information

To attach your file:
- click Reply / Go Advanced / scroll down to Manage Attachments / follow instructions (top of screen)

thanks

[grimey21]

Trying to filter rows that will give me highest sum of Column A. While staying under 140,000 on Column B and under 2,200,000 on Column C.

[kev_]

Is this what you mean:
Max "Sum of Rows in Column A" where "Sum of Same Rows in Column B" < 140,000 AND "Sum Same Rows in Column C" <2,200,000

[FlameRetired]

I'm not sure I grasp the concept.

The total of column B is only 134,953.
The total of column C is 531,856.
Both totals meet the criteria.
That means the sum of range A1:A50 (26,843) meets both criteria.

Does the sample fairly represent what you want to do?

[FlameRetired]

Try this. It will return the range.

$A$1:INDEX($A$1:$A$50,MATCH(2,1/(SUBTOTAL(9,OFFSET(A1,,,ROW($A$1:$A$50)-MIN(ROW($A$1:$A$50))+1,)) < SUBTOTAL(9,OFFSET(B1,,,ROW($A$1:$A$50)-MIN(ROW($A$1:$A$50))+1,)))*(SUBTOTAL(9,OFFSET(A1,,,ROW($A$1:$A$50)-MIN(ROW($A$1:$A$50))+1,)) < SUBTOTAL(9,OFFSET(C1,,,ROW($A$1:$A$50)-MIN(ROW($A$1:$A$50))+1,))),1))

Wrap SUM around it and array enter it. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

=SUM($A$1:INDEX($A$1:$A$50,MATCH(2,1/(SUBTOTAL(9,OFFSET(A1,,,ROW($A$1:$A$50)-MIN(ROW($A$1:$A$50))+1,)) < SUBTOTAL(9,OFFSET(B1,,,ROW($A$1:$A$50)-MIN(ROW($A$1:$A$50))+1,)))*(SUBTOTAL(9,OFFSET(A1,,,ROW($A$1:$A$50)-MIN(ROW($A$1:$A$50))+1,))
< SUBTOTAL(9,OFFSET(C1,,,ROW($A$1:$A$50)-MIN(ROW($A$1:$A$50))+1,))),1)))