MIN / MINIFS with multiple criteria in single row (can't return zero).

[JonCF84]

Hello all,

I'm trying to solve an issue related to finding the minimum value based on multiple criteria in the same range. I've even tried DMIN but nothing has worked. See attached file for example.

Column B = Price, and I want to return the lowest price (excluding zeros) if the color is any of the following - Yellow, Red, Blue. It's also possible there may be a blank cell instead of a color shown.

Column C = Criteria, it's all in the same range and as stated above, it needs to return the min value (excluding zeros) if the price is associated with any of the specified colors or a blank cell.

Thanks for your help in advance. I've been struggling with this one for a while now and I'm ready to move past it.

 

[GraH - Guido]

Jon, did you try a pivot?
I'm puzzled by the way you have presented it, but I believe a simple pivot lay-out returns exactly what you are after.

 

[JonCF84]

GraH,

I appreciate your response. My issue with the pivot table is that I can't drag it down. I need to compare my result to something else directly next to it. Is there any sort of formula that can be used where I can drag it down? I'll put together a better excel file and post it when I have a chance.

 

[Peter Bartholomew]

The problem with one of the formulas is that it tests for the criterion to be simultaneously 'yellow' AND 'red' AND 'blue'.
The following will give an array of results, one for each color
= MINIFS( Price, Price, ">0", Criteria, Color )