# Formula MIN, MAX, Sumproduct, etc.

#### billythedj66

##### Board Regular
Hell all.

I've been racking my brain on this one, and I cannot seem to get it to work:

Worksheet 'Stats' has a list with the following:
Column 'C3:C1003' is a list of locations (Green, Jones, Green, Tom, etc.)
Column 'P3:P1003' is a list with number data (18,18,16,18, etc.)
Column 'BF3:BF1003' is a list with number data (106,133,125,118,etc.)

On another worksheet 'Breakdown', D3 cell has a work location.

In cell c19 I want the following:
The maximum number of 'BF3:BF1003' whereas 'C3:C1003' = D3 and 'P3:P1003'=18.

I tried the following:
=SUMPRODUCT(MAX(('Stats'!\$C\$3:\$C\$1003=\$C\$3),--('Stats'!\$P\$3:\$P\$1003=18)+0,('Stats'!BF\$3:BF\$1003)))

That gave me the maximum number in 'BF3:BF1003' regardless of the criteria.

in C20 I tried the following:
=SUMPRODUCT(MIN(('Stats'!\$C\$3:\$C\$1003=\$C\$3),--('Stats'!\$P\$3:\$P\$1003=18)+0,('Stats'!BF\$3:BF\$1003)))

That gave me a value of 0.

Any ideas would be greatly appreciated.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Replies
0
Views
452
Replies
1
Views
792
Replies
0
Views
462
Replies
3
Views
274
Replies
6
Views
1K

1,181,055
Messages
5,927,862
Members
436,573
Latest member
CMR237

### 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.

### Which adblocker are you using?

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

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