In Maxif, Minif Functions, I discussed finding the maximum or minimum for a subset of data based on certain criteria. If you have more than one criterion, the formulas are roughly the same.
In this example, I have three items in four different PriceZones. I want to find the most expensive and least expensive of each item by PriceZone.
For the max, I simply multiply another array
The first set of parentheses will return zero if there’s no match. Same for the second. The third set returns the prices. When I multiply them together, I get a few non-zero numbers and a bunch of zeros. Then the formula takes the MAX.
If I do the same thing with MIN, I’ll always get zero. When the MIN criterion don’t match, I need to return a string so that MIN will ignore it. To do that, I nest some IFs
That’s still array entered just like MAX. Now I’m returning empty strings (ignored by MIN) when there’s no match and the price when there is a match.
These examples use two variables, but you can go more if you like. The MAX function is only limited to how long you want to wait for your formulas to calculate. The MIN function, because of the nested IFs, is limited to seven in 2003 and earlier. Although I fully expect the Excel Hero to stop by and show us how to get around that limitation.
>Thanks for the suggestion, Melisssa