Max / sumproduct

[Nathalie Sicard]

Hello, it's me again

I have a table with these columns:
A: Zone
B: Territory
C: Product
D: Store format
E: Store name
F: Sales (some data is non numeric)

So what I need to do is a sheet in which I have 2 drop down values: product and storeformat.

Given those drop down values (which might change) I need to display the highest sales values in each territory.

I'm doing this:

=SUMPRODUCT(--(S2M!$C$7:$C$506='Avance Nat'!B3),--(S2M!$L$7:$L$506='Avance Nat'!B6),max(S2M!$AI$7:$AI$506))

But it's definitely not working ='(

Thank you!

 

[xld]

Try this array formula

=MAX(IF((S2M!$C$7:$C$506='Avance Nat'!B3)*(S2M!$L$7:$L$506='Avance Nat'!B6),S2M!$AI$7:$AI$506))

 

[Nathalie Sicard]

OMG! It worked perfectly, thank you very much! <3