# MaxIf without using an array formula

1. ## MaxIf without using an array formula

On this forum (and elsewhere) I had only seen MaxIf when using an array formula:
=Max(If(CriteriaColumn=Criteria,Values,0)) entered with Ctrl+Shift+Enter

I basically stumbled on a way to get a maxif formula to work without array-entering it, by using Index:
=Max(Index((CriteriaColumn=Criteria)*Values,))

It returns the desired results; a maximum by criteria. Attached is an example workbook showing it in action.  Register To Reply

2. ## Re: MaxIf without using an array formula

SUMPRODUCT can also be used in a similar way, i.e.

=SUMPRODUCT(MAX((CriteriaColumn=Criteria)*Values))

...of course neither approach works for MIN because the multiplication of arrays means that you always get zero  Register To Reply

3. ## Re: MaxIf without using an array formula

Yeah, I haven't been able to get a non-array MinIf to work
Thanks for the sumproduct tip, though, I hadn't even considered doing it that way   Register To Reply

4. ## Re: MaxIf without using an array formula

Might not look right because it uses LARGE function, but this will give a "non array MINIF" assuming that value isn't negative

=LARGE(INDEX((CriteriaColumn=Criteria)*Values,0),COUNTIF(CriteriaColumn,Criteria))  Register To Reply

5. ## Re: MaxIf without using an array formula

Sorry to post on such an old thread, but should this formula work with other index formulas?  Register To Reply

6. ## Re: MaxIf without using an array formula

another way to mimic non-array MINIF: ``Please Login or Register  to view this content.``
it will only work for values > 0.  Register To Reply

7. ## Re: MaxIf without using an array formula

Of course, with Excel 2010 we now have AGGREGATE, which will work for a range containing both negative and positive values:

=AGGREGATE(15,6,1/(CriteriaColumn=Criteria)*Values,1)

Regards  Register To Reply

8. ## Re: MaxIf without using an array formula

Hi, and welcome to the forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.  Register To Reply

9. ## Re: MaxIf without using an array formula

Here's a non-array MIN IF that handles zeros and/or negative numbers.

Data Range
 A B C D E 1 Region Value ------ Region Min 2 North -10 North -10 3 North 24 East 0 4 North 37 South 21 5 East 0 West 19 6 East 14 7 East 47 8 South 21 9 South 35 10 South 69 11 West 19 12 West 43 13 West 50

Entered in E2 and copied down:

=MIN(INDEX(((A\$2:A\$13=D2)*B\$2:B\$13)+((A\$2:A\$13<>D2)*1E100),0))  Register To Reply

10. ## Re: MaxIf without using an array formula

Based on Tigeravatar's formula in post #1, changed to give min instead of max:
=MIN(INDEX(--SUBSTITUTE(--(A2:A13=A2),0,10^7)*C2:C13,))  Register To Reply

11. ## Re: MaxIf without using an array formula Originally Posted by Tony Valko Here's a non-array MIN IF that handles zeros and/or negative numbers.

Data Range
 A B C D E 1 Region Value ------ Region Min 2 North -10 North -10 3 North 24 East 0 4 North 37 South 21 5 East 0 West 19 6 East 14 7 East 47 8 South 21 9 South 35 10 South 69 11 West 19 12 West 43 13 West 50

Entered in E2 and copied down:

=MIN(INDEX(((A\$2:A\$13=D2)*B\$2:B\$13)+((A\$2:A\$13<>D2)*1E100),0))
Hi Tony,

Your formula works perfectly for me. Thanks. I had to register just to comment. Could you please shed some light on how the formula works? It was a bit over my head and I could not understand totally.  Register To Reply

12. ## Re: MaxIf without using an array formula

Non-Array MaxIf/MinIf (Handles Negative and Non Whole Numbers)

Minimum Number Greater Than 0 - =LARGE(AP3:AP9,COUNTIF(AP3:AP9,">0"))

Minimum Number - Greater Than 15 - =SMALL(AP3:AP9,(COUNTIF(AP3:AP9,"<=15")+1))

Maximum Number - Less Than 16 - =SMALL(AP3:AP9,COUNTIF(AP3:AP9,"<16"))

If the text is required for the lowest number > 0 (See table below <-------AF-------><----AP---->
3 Number One_______34
4 Number Two_______23
5 Number Three______15
6 Number Four_______12
7 Number Five_______21
8 Number Six________0
9 Number Seven______0

This is the formula:
=INDEX(AF3:AF9,MATCH(LARGE(AP3:AP9,COUNTIF(AP3:AP9,">0")),AP3:AP9,0)) --- Returns ( "Number Four")  Register To Reply

13. ## Re: MaxIf without using an array formula Originally Posted by tigeravatar On this forum (and elsewhere) I had only seen MaxIf when using an array formula:
=Max(If(CriteriaColumn=Criteria,Values,0)) entered with Ctrl+Shift+Enter

I basically stumbled on a way to get a maxif formula to work without array-entering it, by using Index:
=Max(Index((CriteriaColumn=Criteria)*Values,))

It returns the desired results; a maximum by criteria. Attached is an example workbook showing it in action.
Hi,

Thank you for the comment you provided. However, on my end, when I tried the formula, it provides 0 as a result. I hope you could enlighten me on this. Thank you.  Register To Reply

14. ## Re: MaxIf without using an array formula

Hello betina_mahs and Welcome to Excel Forum. We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Let us know if you have any questions.  Register To Reply