Max IF formula?

[billybob]

I run my business using Excel except for inventory and quoting. But want to move to one joined by system. I am starting to build it and have attached a copy "apples".

At the moment I am just focusing on purchases and quoting.

In the stock sheet - I have calculated the total quantity and value purchased per item using SUMIF & the average Minimum Purchase price using AVERAGEIF.

I would like to be able to add min and max values per item which you can easily do in a pivot table (included) - but is there and equivalent formula with a criteria for MAX and MIN.

Any ideas?
Thanks
BB

 

[Luke M]

Welcome to the forum billybob.
Just curious, is there a reason to avoid using a PivotTable? There is a way to do it with formulas, it's just a bit...lengthy.
http://chandoo.org/wp/2012/01/24/formula-forensics-no-008/

 

[billybob]

Luke

Yes the actual spreadsheet will be complicated (nothing like apples) and get then used elsewhere - so I would prefer the formula.

Thanks
BB

 

[Luke M]

Fair enough, although IMO, then things get "complicated", that's when I lean more towards PT's, as they're faster calculating, and easier to adapt. Admittedly, I know I only have a small view of the project. Just trying to give the outsider's opinion that formulas might not be the best way.

 

[NARAYANK991]

Hi ,

I don't think a simple MAX ( IF ( .... ) ) formula is going to make your workbook unduly sluggish ; especially if you have just one criterion.

Switch to tables , and avoid having to create and use named ranges.

See your file.

Narayan