Sumifs and Max/Min Values

[CLG31415]

Here is a glimpse into my data set
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bill[/TD]
[TD]22[/TD]
[TD]$100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bill[/TD]
[TD]21[/TD]
[TD]$150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tim[/TD]
[TD]23[/TD]
[TD]$1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Tim[/TD]
[TD]22[/TD]
[TD]$2000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Jill[/TD]
[TD]18[/TD]
[TD]$1500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Jill[/TD]
[TD]17[/TD]
[TD]$900[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Sue[/TD]
[TD]25[/TD]
[TD]$800[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Sue[/TD]
[TD]24[/TD]
[TD]$200[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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))
[TABLE="class: grid, width: 384"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Bill
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]22
[/TD]
[TD="width: 64, bgcolor: transparent"]$100
[/TD]
[TD="width: 64, bgcolor: transparent"]Bill
[/TD]
[TD="width: 64, bgcolor: transparent"]$100
[/TD]
[TD="width: 64, bgcolor: transparent"]$150
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Bill
[/TD]
[TD="bgcolor: transparent, align: right"]21
[/TD]
[TD="bgcolor: transparent"]$150
[/TD]
[TD="bgcolor: transparent"]Tim
[/TD]
[TD="bgcolor: transparent"]$1000
[/TD]
[TD="bgcolor: transparent"]$2000
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Tim
[/TD]
[TD="bgcolor: transparent, align: right"]23
[/TD]
[TD="bgcolor: transparent"]$1000
[/TD]
[TD="bgcolor: transparent"]Jill
[/TD]
[TD="bgcolor: transparent"]$1500
[/TD]
[TD="bgcolor: transparent"]$900
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Tim
[/TD]
[TD="bgcolor: transparent, align: right"]22
[/TD]
[TD="bgcolor: transparent"]$2000
[/TD]
[TD="bgcolor: transparent"]Sue
[/TD]
[TD="bgcolor: transparent"]$800
[/TD]
[TD="bgcolor: transparent"]$200
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jill
[/TD]
[TD="bgcolor: transparent, align: right"]18
[/TD]
[TD="bgcolor: transparent"]$1500
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jill
[/TD]
[TD="bgcolor: transparent, align: right"]17
[/TD]
[TD="bgcolor: transparent"]$900
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sue
[/TD]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: transparent"]$800
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sue
[/TD]
[TD="bgcolor: transparent, align: right"]24
[/TD]
[TD="bgcolor: transparent"]$200
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

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]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td] Bill[/td][td] 22[/td][td] $100 [/td][/tr]
[tr][td]

2​

[/td][td] Bill[/td][td] 21[/td][td] $150 [/td][/tr]
[tr][td]

3​

[/td][td] Tim[/td][td] 23[/td][td] $1,000 [/td][/tr]
[tr][td]

4​

[/td][td] Tim[/td][td] 22[/td][td] $2,000 [/td][/tr]
[tr][td]

5​

[/td][td] Jill[/td][td] 18[/td][td] $1,500 [/td][/tr]
[tr][td]

6​

[/td][td] Jill[/td][td] 17[/td][td] $900 [/td][/tr]
[tr][td]

7​

[/td][td] Bill[/td][td] 22[/td][td] $55 [/td][/tr]
[tr][td]

8​

[/td][td] Sue[/td][td] 25[/td][td] $800 [/td][/tr]
[tr][td]

9​

[/td][td] Sue[/td][td] 24[/td][td] $200 [/td][/tr]
[/table]

What is the expected outcome for Bill here?