MIN/MAXIFS function with a criteria that returns multiple correct answers

[Mikek1988]

Hi everyone,

I am trying to use some data to find the minimum and maximum values using the MINIFS and MAXIFS function.
However one of my criteria needs to have multiple answers, so the answer of a range could be 'A' or 'B' etc

So far I have got it to return an result for one answer but I also need it to look up other values too as they could be the correct answer instead.

I cannot upload the sheet because it is confidential, any ideas would be greatly appreciated

 

[Mikek1988]

What my equation really needs is a literal 'or' in it so it could be A2orB2orC2 etc but the actual OR function will only return me a true or false answer but I actually need the Minumum number in the range I have selected

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C	D	E	F	G
1	Values	Criteria 1	Criteria 2		Criteria 1	Criteria 2	Max
2	1	A	Yes		A	Yes	18
3	2	B	No		C
4	3	C	Yes		F
5	4	D	Yes
6	5	E	Yes
7	6	F	Yes
8	7	A	Yes
9	8	B	No
10	9	C	No
11	10	D	Yes
12	11	E	Yes
13	12	F	Yes
14	13	A	Yes
15	14	B	Yes
16	15	C	No
17	16	D	No
18	17	E	Yes
19	18	F	Yes
20
Sheet24

Cell Formulas
Range	Formula
G2	G2	=MAX(MAXIFS(A:A,B:B,E2:E4,C:C,F2))

 

[Mikek1988]

Thanks I will try,

I think the issue is all my data is on one sheet and the table I'm filling in which has the criteria is on another sheet and I cannot add anything to that sheet so I am having to use "???" rather than cells on that sheet

 

[Eric W]

You can hard code the values in the formula, see H2:

Book1
	A	B	C	D	E	F	G	H	I
1	Values	Criteria 1	Criteria 2		Criteria 1	Criteria 2	Max	Max v2	Max v3
2	1	A	Yes		A	Yes	18	18	18
3	2	B	No		C
4	3	C	Yes		F
5	4	D	Yes
6	5	E	Yes
7	6	F	Yes
8	7	A	Yes
9	8	B	No
10	9	C	No
11	10	D	Yes
12	11	E	Yes
13	12	F	Yes
14	13	A	Yes
15	14	B	Yes
16	15	C	No
17	16	D	No
18	17	E	Yes
19	18	F	Yes
20
Sheet24

Cell Formulas
Range	Formula
G2	G2	=MAX(MAXIFS(A:A,B:B,E2:E4,C:C,F2))
H2	H2	=MAX(MAXIFS(A:A,B:B,{"A","C","F"},C:C,"Yes"))
I2	I2	=MAX(MAXIFS(A:A,B:B,{"A","C","F"},C:C,{"Yes";"No"}))

Note that with the way Excel works internally, you can only have 2 multiple ranges in an ...IFS function, one horizontal and one vertical. See the I2 formula and how I used semicolons instead of commas in the second array. You can have as many single values as you want.

 

[Mikek1988]

You can hard code the values in the formula, see H2:

Book1
	A	B	C	D	E	F	G	H	I
1	Values	Criteria 1	Criteria 2		Criteria 1	Criteria 2	Max	Max v2	Max v3
2	1	A	Yes		A	Yes	18	18	18
3	2	B	No		C
4	3	C	Yes		F
5	4	D	Yes
6	5	E	Yes
7	6	F	Yes
8	7	A	Yes
9	8	B	No
10	9	C	No
11	10	D	Yes
12	11	E	Yes
13	12	F	Yes
14	13	A	Yes
15	14	B	Yes
16	15	C	No
17	16	D	No
18	17	E	Yes
19	18	F	Yes
20
Sheet24

Cell Formulas
Range	Formula
G2	G2	=MAX(MAXIFS(A:A,B:B,E2:E4,C:C,F2))
H2	H2	=MAX(MAXIFS(A:A,B:B,{"A","C","F"},C:C,"Yes"))
I2	I2	=MAX(MAXIFS(A:A,B:B,{"A","C","F"},C:C,{"Yes";"No"}))

Note that with the way Excel works internally, you can only have 2 multiple ranges in an ...IFS function, one horizontal and one vertical. See the I2 formula and how I used semicolons instead of commas in the second array. You can have as many single values as you want.

Thanks I think H2 might be the solution I didn't know about the curly brackets, what is it exactly that they do?

Everything in my equation has one correct answer except for one criteria which I need to have at least 7 potential answers to check for minimum time

 

[Eric W]

The curly braces define an array constant, or a group of values. Try this experiment. On a blank sheet, put the numbers 1 2 3 4 in A1:B2. Then in another cell, type =A1:B2, but don't hit enter yet. Highlight the A1:B2 part and press F9. That converts it to the value. If you have the US settings, you should see a comma separating columns, and a semicolon separating rows. You can use that same nomenclature in your own formulas directly.

So when you use an array in a formula that normally does not take an array, Excel will calculate the formula 1 time for each entry in the array, giving another array as a result. That's why we need the extra MAX at the front. In my example, it calculates the MAXIFS for "A", then "C", then "F", giving an output array of {13,3,18}, then the outer MAX takes the max value of those. You can see it if you use the Evaluate formula tool.

 

[Mikek1988]

Ok so in my document my values for A C F it will only find a value for one of them so when I use the curly brackets it will look like {0,12,0} and so will take the answer where it has a positive value and ignore the rest if that is a criteria question, is that right? If so then that's great as long as I can have as many as I want

 

[Eric W]

Yes, it should work for you. Try it out and let me know.

 

[Mikek1988]

Yes, it should work for you. Try it out and let me know.

Hi,
I've tried all three of the equations,
In my 2nd column I have multiple locations that I am searching a minimum time against and this is automatically dropping my equation down,
G2 doesn't give me any answers
H2 gives me the results I want but it wants to display the answers in multiple columns for each cell reference within the curly brackets but when I add MIN before the MINIFS it then reverts back to giving me no answers and only the first row filled in but it shows 0
If I change the commas within the curly brackets for semi colons it gives me the minimum time only for the lowest value in the curly brackets but not for each separate row