Min (IF AND) formula accounting for text string?

[Thermalunit]

I joined the board as I am completely stumped by an equation and have been unable to find a solution on any board or through an hour or so of head scratching. I wondered if someone more familiar with Excel may be kind enough to help?

I'm trying to find the minimum value from a table of data if two separate conditions are met. One of the conditions needs to search for a string of text.

I have tried using: =MIN(($K$3:$K$2152=A3)*($ER$3:$ER$2152="*Enable*")*($EG$3:$EG$2152)) as an array but I just get a 0 in return. I can see from the spreadsheet that 0 is not the correct answer.

Any help would be much appreciated.

 

[Thermalunit]

Also tried: =MIN(IF(($K$3:$K$2152=A3)*(ISNUMBER(SEARCH(”*Enabled*”,$ER$3:$ER$2152))),$EG$3:$EG$2152)) but that returns a 0 also

 

[brans1982]

Try...

=SUMPRODUCT((MIN($K$3:$K$2152)=A3)*($ER$3:$ER$2152="*Enable*"))

 

[brans1982]

Try...

=SUMPRODUCT((MIN($K$3:$K$2152)=A3)*($ER$3:$ER$2152="*Enable*"))

Sorry...

=SUMPRODUCT((MIN($K$3:$K$2152)=A3)*($ER$3:$ER$2152="*Enable*")*($EG$3:$EG$2152))

 

[Andrew Poulsom]

Welcome to MrExcel.

Are you trying to use the asterisks around Enable as wildcard characters? If so you will need to use something like:

=MIN(($K$3:$K$2152=A3)*(ISNUMBER(SEARCH("Enable",$ER$3:$ER$2152)))*($EG$3:$EG$2152))

 

[Thermalunit]

Thanks Brans but the MIN value needs to come from the EG column if the conditions in K and ER are met.

 

[Thermalunit]

Thanks Andrew and Brans but neither solution worked. It still returns a 0 (I can see that the answer is 1.42.

Andrew - thanks for the welcome. Yes I had used the * as wildcard characters.

Any other ideas much appreciated.

 

[Andrew Poulsom]

Try:

=MIN(IF(($K$3:$K$12=A3)*(ISNUMBER(SEARCH("Enable",$ER$3:$ER$12))),($EG$3:$EG$12)))

 

[Thermalunit]

Thanks again Andrew - tried it but still returns a 0

I can't see any formatting issues in the Excel field. And the search works if I limit it to one field only. It looks to be having problems identifying cells that contain the word "enabled". The cells with "enabled" in either contain the single word "enable" or within a sentence of eight words.

Strange!

 

[Andrew Poulsom]

Do you have any blank cells in column EG that match the criteria? Can you give some examples of the text in column ER?