Minimum if range contains text

[Manny Singh]

Hi All,

Could you please help write a formula for problem described in attached excel file?

Thanks,
Mani

 

[PaulF]

If you have Excel 2016:

=MINIFS(H3:H9,A3:A9,"Fill*")

 

[PaulF]

If you don't have Excel 2016:

=MIN(IF(ISNUMBER(SEARCH("Fill*",A3:A9)),H3:H9))

CSE (Control Shift Enter) - Array Formula

 

[bosco_yip]

Maybe, one of :

=AGGREGATE(15,6,B3:B9/(LEFT(A3:A9,4)="Fill"),1)

=AGGREGATE(15,6,B3:B9/ISNUMBER(FIND("Fill",A3:A9)),1)

Regards
Bosco

 

[Manny Singh]

If you don't have Excel 2016:

=MIN(IF(ISNUMBER(SEARCH("Fill*",A3:A9)),H3:H9))

CSE (Control Shift Enter) - Array Formula

Hi Paul,

Works perfect, thanks. Is it possible to adjust formula so it ignores blank cells and not return zero?

 

[Khalid NGO]

Is it possible to adjust formula so it ignores blank cells and not return zero?

Hi,

Just add one more condition to Paul's formula:
=MIN(IF(ISNUMBER(SEARCH("Fill*",A3:A9))*B3:B9>0,B3:B9))

{array formula}

Regards,

 

[Manny Singh]

Hi,

Just add one more condition to Paul's formula:
=MIN(IF(ISNUMBER(SEARCH("Fill*",A3:A9))*B3:B9>0,B3:B9))

{array formula}

Regards,

Thank Khalid, works perfect.