Max(if) to return another columns value

confused in Frankfurt

Board Regular
Joined
Oct 11, 2010
Messages
53
Hi There,
I have a problem I need to not only return the highest value per sales rep but the name of the deal.

Opportunity Name
Opportunity OwnerUnique Sales Price
Arriva_MittelstandAndrea Junge130.000
Siemens-CIT-ARIS Design ServerDenis Wachsmuth17.600
Telefónica AlfabetDirk Klimas500.000
DB Systel_TerminalemulationAndrea Junge800.000

<tbody>
</tbody>


<tbody>
</tbody>

The following formula returns the highest deal in the portfolio but how to get the name of the deal is the problem.
=MAXA(if('Pipeline SF '!H:H=Highlights!$B60;'Pipeline SF '!AI:AI;0))

Regards
Sarah:confused:
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Here's my set-up


Excel 2010
ABC
1Opportunity NameOpportunity OwnerUnique Sales Price
2Arriva_MittelstandAndrea Junge130
3Siemens-CIT-ARIS Design ServerDenis Wachsmuth17.6
4Telefnica AlfabetDirk Klimas500
5DB Systel_TerminalemulationAndrea Junge800
6
7OWNERNAMEMAXIMUM
8Andrea JungeDB Systel_Terminalemulation800
Sheet1
Cell Formulas
RangeFormula
B8{=INDEX($A$2:$A$5,MATCH(2,(B2:B5=$A$8)+(C2:C5=$C$8),0))}
C8{=MAX(IF(B2:B5=A8,C2:C5))}
Press CTRL+SHIFT+ENTER to enter array formulas.


The "2" in the MATCH formula is based on the fact that if the two condition are met i.e NAME and Value, you have TRUE+TRUE which is 2 (something like that)
 
Upvote 0
Can the maximum value appear more than once for each owner? e.g can you have Andrew Junge, 800 and on another line Andrew Junge, 800?

My formula is matching both the name and the max value for that specific name
 
Upvote 0
Hi,


in the example: in C1:C1000 names of the deal

=INDEX('Pipeline SF '!C1:C1000;MATCH(MAXA(if('Pipeline SF '!H:H=Highlights!$B60;'Pipeline SF '!AI1:AI1000;0))&Highlights!$B60;'Pipeline SF '!AI1:AI1000&'Pipeline SF '!H1:H1000;0))

to be confirmed with control+shift+enter

You'll have for sure to adjust formula references.

I hope it helps
 
Upvote 0
In the event there are multiple matches, you could use the array formula in E2 and drag downwards


Excel 2010
ABCDE
1Opportunity NameOpportunity OwnerUnique Sales PriceMATCHES
2Arriva_MittelstandAndrea Junge800Arriva_Mittelstand
3Siemens-CIT-ARIS Design ServerDenis Wachsmuth17.6DB Systel_Terminalemulation
4Telefnica AlfabetDirk Klimas500
5DB Systel_TerminalemulationAndrea Junge760
6Siemens-CIT-ARIS Design ServerDenis Wachsmuth17.6
7Telefnica AlfabetDirk Klimas500
8DB Systel_TerminalemulationAndrea Junge800
9
10
11OWNERMAXIMUM
12Andrea Junge800
Sheet1
Cell Formulas
RangeFormula
E2{=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$A$12,IF($C$2:$C$8=$B$12,ROW($A$2:$A$8)-ROW($A$2)+1)),ROW(A1))),"")}
B12{=MAX(IF(B2:B8=A12,C2:C8))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
Alternatively you can have pivot table. Select column A , B , C and go to insert pivot table. Follow the instruction as per your needs. Remember that Pivot can be in existing worksheet.


1. Move Unique sales price to ∑ values
2. Move Opportunity Name to Row Labels
3. Under ∑ values you may see the count, so select the drop down | Value field settings | select sum
4. You will see row labels on the table, select drop down | Value Filters | Top 10 | select 1 instead of 10.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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