# 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 want the minimum number, 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

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

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

For statistics like Max, Min, Average, etc., a SumProduct formula is not the right solution...

=MAX(IF('Stats'!\$C\$3:\$C\$1003=\$C\$3,IF('Stats'!\$P\$3:\$P\$1003=18,'Stats'!BF\$3:BF\$1003)))

In C20, I want the minimum number, 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.

Replace MAX with MIN in the above formula.

#### billythedj66

##### Board Regular
THANKS!!!!!!!!!!!!!!!

Replies
3
Views
282
Replies
0
Views
473
Replies
1
Views
854
Replies
8
Views
118
Replies
0
Views
417

1,186,828
Messages
5,960,029
Members
438,458
Latest member
manojkumarat1999

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