MINIFS using OR function

Question

I’m trying to find the minimum date in column B if any cell in columns E, F, or G contains the word banana.

Give the minimum date in column B
IF: column E contains “banana”
OR IF: column F contains “banana”
OR IF: column G contains “banana”

I’m not quite sure how to nest these statements or if an array formula is needed. I tried a variety of nested MAX(IF(OR formulas but kept getting a parse error. Also tried an array but also got a parse error. Appreciate help trying to understand how to get this formula.

score 3 · Accepted Answer · edited Sep 08 '18 at 07:28

3

Also

=min(filter(B:B,(D:D="Banana")+(E:E="Banana")+(F:F="Banana")))

(you can only get an AND condition out of MINIFS - see this question for how to get an OR condition generally)

Here is a version that finds "Banana split" etc.

=min(filter(B:B,isnumber(search("Banana",D:D))
               +isnumber(search("Banana",E:E))
               +isnumber(search("Banana",F:F))))

edited Sep 08 '18 at 07:28

player0

124,011
12
67
124

answered Aug 21 '18 at 08:53

Tom Sharpe

30,727
5
24
37

Thank you very much! I just spent a couple hours trying to figure this out when I should've just used your formula. Many, many thanks! – que syrah sarah Aug 21 '18 at 18:54
You're welcome! I thought Dmin was also a good idea, but I couldn't get it to work with empty cells. – Tom Sharpe Aug 21 '18 at 20:56

score 1 · Answer 2 · answered Aug 21 '18 at 08:37

1

you can try something like:

=MINIFS(B1:B, E1:E, "banana")
=MINIFS(B1:B, F1:F, "banana")
=MINIFS(B1:B, G1:G, "banana")

and then:

=MIN(I1:K1)

https://docs.google.com/spreadsheets/d/

answered Aug 21 '18 at 08:37

player0

124,011
12
67
124

thanks for doing this! i liked your nested formula. however, i don't think MINIFS allows for the **contains** condition. when i updated your formula to look for `"*banana*"` it came up with an error. Here's the modified formula I used `=min(filter(B3:B,(G3:G="*banana*")+(E3:E="*banana*")+(F3:F="*banana*")))` (see test doc). is there a way to get it to look for min values in B when either columns E, F, or G contain banana? (not the exact word "banana" but "banana peel," "banana split", "banana tree", etc) – que syrah sarah Aug 21 '18 at 17:40
your first solution creates 4 new cells; 3 individual cells contain their own `MINIFS` formula. then you find the `MINIFS` between those 3. that works even with `"*banana*"` but for some reason, the nested version does not allow the **contains** qualifier ( * ). am i doing something wrong? is it possible to use your nested formula with the wildcards/*? – que syrah sarah Aug 21 '18 at 18:00
I think you are referring to my answer with the filter, I will edit a 'contains' version into it – Tom Sharpe Aug 21 '18 at 18:05
what about using `DMIN`? that allows for wildcards. i've never used it but will try something out – que syrah sarah Aug 21 '18 at 18:08

MINIFS using OR function

2 Answers2