Find MIN number array

[Kellis]

Hi all,

I need to find the minimum number in column if it is the same reference number and for the life of me cannnot work it out.

I can find the minimum of the whole column but do not know how to get the formula to look at the reference no also. Thought a countifs would work but no joy.

(IF(ISBLANK(J2),"",IFERROR(SMALL(Q:Q,COUNTIF($Q$1:$Q$998,0)+1),"")))

This formula would return 5 not the result below. Any help0 much appreciated.

Ref No MIN
12 7 6
12 6 6
12 8 6
14 10 5
14 5 5
14 6 5

Thanks

 

[bosco_yip]

C2, copied down :

=AGGREGATE(15,6,B$2:B$7/(A$2:A$7=A2),1)

Regards
Bosco

 

[Peter Bartholomew]

With Excel 2013 or earlier:
= MIN( selectedNos )
where the named formula 'selectedNos' refers to
= IF( Table1[Ref]=Table1[@Ref], Table1[No] )

With Excel 2016 or 365:
= MINIFS( [No], [Ref], [@Ref] )

 

[GraH - Guido]

Peter, I have Office 2016 and Excel does not know about MINIF or MINIFS. Only the 365 license seems to offer it.

 

[Peter Bartholomew]

@GraH - Guido
Thanks for the heads up on that. I checked the laptop I had used to verify the formula and, sure enough, it was 365 and not Office 2016. Possibly the same goes for 'Get and Transform' (using Office 2010 I have PQ as an add-in) and TEXTJOIN which I believe are also worthwhile enhancements.

One thing I like about the IFS functions is their ability to reduce the dimensionality of conditional aggregations so that
{= MINIFS( [No], [Ref], [Ref] )}
returns an entire array of minima as one object.