MAXIF

[beroth]

Is there a formula that functions as SUMIF but returns the MAX of the sum_range?

 

[Colin Legg]

Unfortunately there isn't a

MAXIF() function in the current versions of Excel. You can use an array formula which combines the [code]MAX() and [code]IF() functions to get the same result, or you can use a helper column. 


Suppose you have some data where you have a list of words in column A and you have a list of numbers in column B.


Array formula approach:


Get the max value from B1:B100 where column A equals "SomeValue":

[code]=MAX(IF(A1:A100="SomeValue",B1:B100))

This is an array formula so when you type it into the formula bar you must complete the entry with CTRL+SHIFT+ENTER instead of the usual ENTER. If done correctly, Excel will automatically surround the formula with curly braces { }. Note that you can't put in these curly braces yourself.


Helper column approach:


Put this formula in C1 and fill down the column:

=IF(A1="SomeValue",B1)[/code]


Then to get the max:

=MAX(C1:C100)[/code]


There are some other options too. If your data is well structured like a table then you could use the DMAX()[/code] function. If you're willing to use a non-formula approach you could use a pivot table.


I hope that gives you some ideas.

 

[beroth]

Can you further explain the { }. That is what I was currently using but the calculations appear to move from one cell, with the formula, to the next rather slowly.

 

[bobhc]

Good day beroth


Bit of a read but this link will explain the use of the curly brackets{}


http://www.cpearson.com/Excel/ArrayFormulas.aspx

 

[Colin Legg]

Hi beroth,


Array formulas can be slow to calculate if:

-they reference large ranges

-you use a lot of them in your worksheet

-they contain expensive functions


So generally:

-Avoid using whole column references such as A:A or B:B (whole columns aren't allowed in array formulas prior to Excel 2007 anyway)

-Avoid using a lot of array formulas

-Avoid slow calculating functions

-Avoid volatile functions (these recalculate every time a calculation event occurs even if no precedent cells have changed)


If your workbook is still calculating slowly then I suggest either using a helper column (example in last post), using

DMAX()

or reverting to a non-formula solution such as a pivot table.

 

[James Perry]

Hello Beroth.....Pls try out the following formulas. Hope that helps.


1. =MAX(INDEX((A1:A25=D1)*B1:B25,))

2. =LARGE(IF(A1:A25=D1,B1:B25),1))


All the best!

 

[beroth]

I avoided using whole column references and there is no recognizable lag time. Much better. Thanks Colin

 

[James Perry]

Hello Beroth,

Your Q - is there a formula that functions as SUMIF but returns the MAX of the sum_range?

Will the following formula serve ur purpose?

=MAX(IF(A1:A9>5,A1:A9,""))

Best wishes.