Sumifs and Max/Min Values

[CLG31415]

Here is a glimpse into my data set

	A	B	C	D
1	Bill	22	$100
2	Bill	21	$150
3	Tim	23	$1000
4	Tim	22	$2000
5	Jill	18	$1500
6	Jill	17	$900
7	Sue	25	$800
8	Sue	24	$200

<tbody>
</tbody>

I'm trying to look up the dollar amount for each person based on that individual's maximum variable in Column B. So for example. I'm trying to do a SUMIF to look up Tim in column A, then looking up the max value for Tim in column B (that would be 23), and then I want to get the dollar amount from Column C that corresponds to that max value. So for my example, that would be $1000. Then I want to reproduce this process but I'm going to want the min value instead of the max for each individual.

My data is currently sorted to group the names together. Once the names are in order, it sorts Column B from high to low.

Currently my formula looks like this to get the greatest value:
=SUMIFS('TimeSheet'!C:C,'TimeSheet'!A:A,"Tim",'TimeSheet'!B:B,"23")

and like this for the minimum value:

=SUMIFS('TimeSheet'!C:C,'TimeSheet'!A:A,"Tim",'TimeSheet'!B:B,23-1)


Is there a way to incorporate a max/min into my SUMIF that will produce the correct answer so I don't have to "hard-code" a number into my formula or a number minus one in the case of wanting the minimum?

 

[jeffreybrown]

How about...

=INDEX(TimeSheet!C1:C8,MATCH(MAX(IF(TimeSheet!A1:A8="Tim",TimeSheet!B1:B8)),TimeSheet!B1:B8,0))

Entered with: Ctrl + Shift + Enter

Of course for Min just replace Max in the formula above

 

[godsaaint]

Try:

Array Formula in E1(Ctrl-Shift-Enter): =INDEX($C$1:$C$8,MATCH(D1&LARGE(IF($A$1:$A$8=D1,$B$1:$B$8),1),$A$1:$A$8&$B$1:$B$8,0))
Array Formula in F1(Ctrl-Shift-Enter): =INDEX($C$1:$C$8,MATCH(D1&SMALL(IF($A$1:$A$8=D1,$B$1:$B$8),1),$A$1:$A$8&$B$1:$B$8,0))

Bill	22	$100	Bill	$100	$150
Bill	21	$150	Tim	$1000	$2000
Tim	23	$1000	Jill	$1500	$900
Tim	22	$2000	Sue	$800	$200
Jill	18	$1500
Jill	17	$900
Sue	25	$800
Sue	24	$200

<tbody>
</tbody>

Just add the sheet reference

EDIT:

How about...

=INDEX(TimeSheet!C1:C8,MATCH(MAX(IF(TimeSheet!A1:A8="Tim",TimeSheet!B1:B8)),TimeSheet!B1:B8,0))

Entered with: Ctrl + Shift + Enter

Of course for Min just replace Max in the formula above

Careful here, the MAX function in this example with Tim is gonna return 23 which will return the correct amount. However, if further down the list, there is another name with another 23 as the max number, the INDEX will return the amount corresponding to Tim's 23

 

[Aladin Akyurek]

Row\Col	A​	B​	C​
1​	Bill	22	$100
2​	Bill	21	$150
3​	Tim	23	$1,000
4​	Tim	22	$2,000
5​	Jill	18	$1,500
6​	Jill	17	$900
7​	Bill	22	$55
8​	Sue	25	$800
9​	Sue	24	$200

What is the expected outcome for Bill here?