Lookup across non-contiguous range

[matt-gilbert]

Hi all,
I have a quote comparison sheet which has a section in which to analyze supplier quotes minimum, average and maximum costs. The problem I have is that I need to manually adjust formula when using for another commodity type. I would like this formula to automatically adjust for different commodities. I have attached sample spreadsheet which further details what I'm after.
Any help would be very much appreciated. Thanks in advance. Matt

 

[vletm]

matt-gilbert​

Something like those below yellow cells...
... based Your given sample data.

 

[p45cal]

I would do it slightly differently from @vletm; in I6:

=MINIFS($O6:$AD6,$O$3:$AD$3,$B6,$O6:$AD6,"<>")

in K6:

=AVERAGEIFS($O6:$AD6,$O$3:$AD$3,$B6,$O6:$AD6,"<>")

and in M6:

=MAXIFS($O6:$AD6,$O$3:$AD$3,$B6,$O6:$AD6,"<>")

These can be copied down.
However, this relies on items which aren't quoted being empty cells (columns O,Q,S,U,W,Y etc.) rather than being zero, because after all, zero is a valid price (free), but an empty cell isn't.

 

[matt-gilbert]

matt-gilbert​

Something like those below yellow cells...
... based Your given sample data.

Thanks @vletm I didn't even consider the MINIFS, AVERAGEIFS and MAXIFS.
I did make a slight tweak to your formulas as follows; in I6:

=MINIFS($O6:$AD6,$O$3:$AD$3,$B$6,$O6:$AD6,">0")

in K6:

=AVERAGEIFS($O6:$AD6,$O$3:$AD$3,$B$6,$O6:$AD6,">0")

and in M6:

=MAXIFS($O6:$AD6,$O$3:$AD$3,$B$6,$O6:$AD6,">0")

Your criteria_range1 was incorrectly $O$4:$AD$4 (should have been $O$3:$AD$3) and your criteria1 was $O$4m (changed to $B6 as per @p45cal suggestion).
Thank you very much for your prompt reply! Matt

 

[matt-gilbert]

I would do it slightly differently from @vletm; in I6:

=MINIFS($O6:$AD6,$O$3:$AD$3,$B6,$O6:$AD6,"<>")

in K6:

=AVERAGEIFS($O6:$AD6,$O$3:$AD$3,$B6,$O6:$AD6,"<>")

and in M6:

=MAXIFS($O6:$AD6,$O$3:$AD$3,$B6,$O6:$AD6,"<>")

These can be copied down.
However, this relies on items which aren't quoted being empty cells (columns O,Q,S,U,W,Y etc.) rather than being zero, because after all, zero is a valid price (free), but an empty cell isn't.

Thanks @p45cal . Refer my reply above to @vletm . I have used both of your suggestions. Appreciate your help! Matt

 

[vletm]

matt-gilbert​

# Those works with $O$3 too, because You have values only in one Commodity in time; checks $/unit -columns.

p45cal​

# case empty or zero
eg row 8 has values: 3.1 / - / - / 2.3
my version min gives 2.3 (as expected) , but I would like to show it as zero
same kind of case is with average ... is it =5.4/2 (as expected) or = 5.4/4
For me, zeros are valid values but too many would like to skip those.