SUMIFs and MAX

[Pavan Kumar Sistla]

Dear All,

The excel has two columns NO. & QTY. I would like to retrieve the NO. with maximum quantity. There is a repetition of numbers too. Please suggest a formula.

Thanks,
Pavan.

 

[Chihiro]

Try...
=SUMIF(B3:B12824,MODE.SNGL(B3:B12824),C3:C12824)

Edit: Oh wait, I think I misunderstood your question...

Why not just use PivotTable?

 

[Pavan Kumar Sistla]

Thank you, however, this does not solve all the queries.

 

[Chihiro]

Hmm, PivotTable will give No. & Max Quantity. What else are you looking for?

 

[Pavan Kumar Sistla]

I want formulas to do the job. Pivot table have already been generated in the 1st sheet of the file which I uploaded

 

[Chihiro]

Why the formula? Pivot is far more efficient at this sort of analysis.

To Find No. Corresponding to Max Quantity
=INDEX($B$3:$B$12824,MATCH(E3&"_"&MAX(IF($A$3:$A$12824=E3,$C$3:$C$12824)),$A$3:$A$12824&"_"&$C$3:$C$12824,0))

Max Quantity
=MAX(IF($A$3:$A$12824=E3,$C$3:$C$12824))

Both confirmed as Array.

 

[NARAYANK991]

Hi ,

See this file ; I have not done the MIN values.

Narayan

 

[Pavan Kumar Sistla]

Thank you very much Narayan