Max If statement with multiple criteria based on specific text in a cell Issue

[Ahldie333]

Hello,


I have been having some difficulty for a few days now with this issue. I am attempting to find the Max for an age based on multiple criteria some excluding and including certain texts from the cells.


I tried building it like the countifs statement where you can search for certain text in the cell and either exclude or include that in the count based off of that text listed in the "* *"
for example


I used the formula {MAX(IF($A:$A="*White*";IF($B:$B<>"*AA*";$C:$C)))} to calculate the max age of all camry's that are white and that do not have AA in their number, if true then to max the age column.

column A has car names such as sky blue camry, snow white camry, bright white camry.

and column B has random numbers like 1123AA, 1123BB, 112,BB, 11AA11 and I dont want to include in the max if the cell has AA.

and column C has the ages of the car.


When i do that with the ctrl shift enter, i get a value returned as 0 everytime. But as soon as I take off the * * it works. So im wondering if there is another way to find the max number using multiple criteria using certain text found in the cell.


any help would be much appreciated :D

 

[FormR]

Hi, try like this, which requires CSE.

=MAX(IF(ISNUMBER(SEARCH("White";$A1:$A1000));IF(NOT(ISNUMBER(SEARCH("AA";$B1:$B1000)));$C1:$C1000)))

Note: for efficiency reasons you should avoid using full column references when using array processing functions.

 

[Ahldie333]

Great! thanks so much, worked like a charm!

 

[Ahldie333]

Hey again, another quick question, what would it look like if i wanted to add in more criteria?

 

[FormR]

Hi, it would depend what those extra criteria were. For another condition based on an additional column you would nest in another IF function.