|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 are always entered 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:
Now, regarding the statements:
"I feel that the essence is that SUMPRODUCT acts on arrays."
"...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.