Formula MIN, MAX, Sumproduct, etc.

[billythedj66]

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.

 

[Aladin Akyurek]

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

Instead control+shift+enter:

=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]

THANKS!!!!!!!!!!!!!!!