I am making a spreadsheet where I need to find the largest number in a certain section (S9:S28) if a certain criteria fits. I tried: =MAXIF(S9:S28,1,W9:W28) Thinking it would give me the maximum number that was in cells W9 through W28 if there was a "1" in cells S9:S28 but it gave me an error. Any Ideas?

Excel does not provide a MAXIF function. You can do it with an array formula:

=MAX((S9:S28=1)*(W9:W28))

entered with Ctrl-Shift-Enter to place the {} around it

or a non-array formula:

=SUMPRODUCT(MAX((S9:S28=1)*(W9:W28)))

I hope that helps.

Hi, MAXIF isn't an Excel function, which would explain why you are getting an error - you didn't say what the error was, but I presume it was #NAME?.

From the 'formula' you posted, am I correct in thinking that you are trying to find the largest value in the range of cell W9 to W28, using only the cells where the value in the cell in column S in the corresponding row is 1.

Try this: =SUMPRODUCT(MAX((S9:S28=1)*(W9:W28)))

Regards

Hi DerbyDad03, You got there first !

I do have a comment about SUMPRODUCT()

You refer to it as anon-array formula, but it really is an array formula as it handles successive rows in the ranges passed to it, independently.I know that it isn't entered with the Ctrl+Shift+Enter keys which are required to make a regular formula use the array - row-by-row approach.

I just think of SUMPRODUCT() as an array formula that doesn't need to be entered using the three keys.

Regards

Humar

Humar, I understand what you are saying but I think it's a matter of semantics.

As far as I know - speaking in Excel terms only - a formula is not referred to as an "array formula" unless it is entered with Ctrl-Shift-Enter.

While there are certainly formulas that can work with arrays (e.g. SUMPRODUCT) I don't believe that they are typically referred to as "array formulas".

As Chip Pearson puts it:

"SUMPRODUCT is designed to take arrays, and SUM is not.

You "force" SUM to take arrays by using an array formula (Ctrl+Shift+Enter)."In his discussion of Array Formulas, he does say:

"An array formula is a formula that works with an array, or series, of data values rather than a single data value."

which is roughly the same as you described SUMPRODUCT, but he follows that with:

"To enter a formula as an array formula, type the formula in the cell and press the CTRL SHIFT and ENTER keys at the same time rather then just ENTER."

That's something you don't do with SUMPRODUCT.

I've also read some discussions regarding the speed of Ctrl-Shift-Enter array formulas vs. getting the same results using a "function that can work with arrays". The Ctrl-Shift-Enter formulas are slower and more resource intensive.

Just to avoid confusion, I only refer to array formulas as those that must be entered with Ctrl-Shift-Enter in order to return the correct value. I could be wrong, but I think that that is the "standard" definition.

Hi, I feel that the essence is that SUMPRDUCT acts on arrays.

So I consider it to be an array formula that just happens not to need to be entered with Ctrl+Shift+Enter

Ctrl+Shift+Enter makes some formulas act on arrays, and they are then termed array formulas.

My point then, is not that you

shouldrefer to SUMPRODUCT as an array formula, rather that you shouldn't say itisn'tan array formula.Happy days ...

Regards

Humar

Thank You very much. It worked Perfectly!!

My wife says I should have been a lawyer... re:

My point then, is not that you should refer to SUMPRODUCT as an array formula, rather that you shouldn't say it isn't an array formula.Let's start by clarifying the terminology for anyone that might be following this thread:

SUMPRODUCT is a

function.=SUMPRODUCT(MAX((S9:S28=1)*(W9:W28))) is a

formula.I'll admit that I have been guilty of using the terms incorrectly on occasion. However, we need to be clear about those definitions since they are the basis for our discussion.

Now let's look at the definition of an "array formula" as it applies to Excel.

The following quote is from Bob Umlas. If we can't trust Bob Umlas, who can we trust? ;-)

"Array formulas are very powerful formulas which can summarize data on a worksheet...They are always entered via holding the Ctrl and Shift keys before pressing enter.

Note the word

always. If Array Formulas arealwaysentered via Ctrl-Shift-Enter, then any formula that isn't entered that way isn't an array formula.John Walkenbach uses the word

must:"The key difference is that when using array formulas, you must press CTRL+SHIFT+ENTER to enter your formulas."

Again, if we don't use CTRL+SHIFT+ENTER it's not considered an array formula - as defined in Excel.

Therefore, when SUMPRODUCT (or any other function) is used in a formula, the formula is not an array formula unless entered with CTRL-SHIFT-DELETE.

For that reason I stand by my claim that this is not an array formula:

=SUMPRODUCT(MAX((S9:S28=1)*(W9:W28)))

Now, regarding the statements:

"I feel that the essence is that SUMPRODUCT acts on arrays."

and

"...you shouldn't say it isn't an array formula"

If you took any logic courses in school you might remember questions like this:

- Auto-mechanics work on cars.

- Auto-mechanics are required to be certified.

- I work on cars.

- I am not certified.Q: Am I an auto-mechanic?

It's OK if you say to me "You are not an auto-mechanic" - even though I work on cars - because I don't meet the certification criteria.

Therefore, IMO, it's OK to say that the SUMPRODUCT formula that I offered "is not an array formula" - even though it acts on arrays - since it does not meet the CTRL+SHIFT+ENTER criteria which, by definition, is required.

Hi, Yes:

SUMPRODUCT is a function.

=SUMPRODUCT(MAX((S9:S28=1)*(W9:W28))) is a formula.

I didn't refer to SUMPRODUCT()the functionas a formula, I was, in your legal terms, referring to how you used the term, You refered to it as anon-array formula.You also say that I have used the falacious argument using your 'auto mechanic' analogy. Not so, I was saying that I, as an individual, didn't have the authority to tell you or anyone else, to not call SUMPRODUCT (actually your

=SUMPRODUCT(MAX((S9:S28=1)*(W9:W28)))an Array formula.So lets come to this: "since it does not meet the CTRL+SHIFT+ENTER criteria which, by definition, is required".

I would argue M'lord, that the fact that SUMPRODUCT() does not meet the criteria "CTRL+SHIFT+ENTER", is not a reason to say that it is not an array function, or when part of a construct including "=" and address ranges etc., an array formula.

It really matters not a jot what criteria are used to define how a function becomes an array formula such as

"Array formulas are very powerful formulas which can summarize data on a worksheet...They are always entered via holding the Ctrl and Shift keys before pressing enter.

A function cannot become an array formula until it is part of a formula, at which point CTRL+SHIFT+ENTER makes it an array formula.The function SUMPRODUCT() becomes an array formula as soon as it starts with "=" and includes ranges etc. It acts on arrays, and does just what "=MAX((S9:S28=1)*(W9:W28))" does once it is converted from a MAX function in a regular formula to the MAX function in an Array formula.

In preliminary conclusion, SUMPRODUCT when part of a formula, is an array formula - it does not act as a regular function within the formula, as it calculates per row first and then handles the per row results next (this is not intended to be a scientific description of how array formulas work). As a result SUMPRODUCT() is at least an array-type function, and when used in the normal way in a formula is an array formula.

With regards to comments such as:Array formulas are very powerful formulas which can summarize data on a worksheet...They are always entered via holding the Ctrl and Shift keys before pressing enter. is a sentence which should be taken in context - it applies to the method used to enable a regular function or functions to be used in the array context. The fact that

theyare always entered ... refers to regular formulas being used as array formulas. It does not say that any other function or formula cannot be an array function or formula.My conclusion remains the same - you should not say that a formula using SUMPRODUCT "is a non-array formula".

SUMPRODUCT (within a formula) is an array formula due to the way it calculates and returns a result. It just happens not to require the additional step of using CTRL+SHIFT+ENTER to convert it from a regular formula to an array formula.

Regards

We're just going to have to agree to disagree on this point. However, in defense of my honor, I must take exception to the following statements:

1 -

I didn't refer to SUMPRODUCT() the function as a formula, I was, in your legal terms, referring to how you used the term, You refered toitas a non-array formula.Based on standard English language constructs, I can only assume that the

itin that statement refers to the SUMPRODUCT function.In Response # 1 I offered a

formulausing the SUMPRODUCT function and stated thatit(the formula) was a non-array formula.2 -

you should not say that a formula using SUMPRODUCT "is a non-array formula"Once again, that is not what I said. I did not say that

aformula using SUMPRODUCT is not an array formula, I said that the formula I offered [=SUMPRODUCT(MAX((S9:S28=1)*(W9:W28)))] was not an array formula.There are certainly formulas using SUMPRODUCT that

canbe array formulas, as long as they are entered via CTRL-SHIFT-ENTERThat, I believe is where we disagree, and that, I believe is where we must leave this.

Ask Your Question

Weekly Poll