The link to a thread
http://www.mrexcel.com/wwwboard/messages/8961.html
on the original board (now a CD) is made available on the current board.
The syntax of the target is...
SUMPRODUCT(Array1,Array2,Array3,...)
where Array1,... can also be read as Reference1, Reference2,... etc. All array arguments must be of
numeric type.
As is clear from above, the comma
is the list separator as in other functions, so there is nothing strange about it.
The formula in your example
[a]
=SUMPRODUCT((A1:A5="department")*(B1:B5="Team A"),C1:C5)
consists of 2 arrays:
[a.1]
(A1:A5="department")*(B1:B5="Team A")
[a.2]
C1:C5
Multiplication in [a.1] produces the first numeric array. When multiplied, the constant arrays of logicals results in a numeric array of 1's and 0's by coercion. So we have in [a] 2 numeric arrays that SUMPRODUCT multiplies and then sums.
If you'd be kosher, you should have 3 arrays...
=SUMPRODUCT((A1:A5="department")+0,(B1:B5="Team A")+0,C1:C5)
that is,
[b.1]
(A1:A5="department")+0
[b.2]
(B1:B5="Team A")+0
[b.3]
C1:C5
The arrays in [b.1] and [b.2] are indeed arrays of numeric type. Adding a 0 to an array of logicals coerce them into arrays of logicals... TRUE+0 ==> 1 and FALSE+0 ==> 0.
SUMPRODUCT multiplies numeric arrays [b.1], [b.2], and [b.3] and then sums.
Often you see the following...
[c]
=SUMPRODUCT((A1:A5="department")*(B1:B5="Team A")*C1:C5)
without comma. This leads after multiplication, again thru coercion, to just one numeric array which SUMPRODUCT then sums (that is, behaves like SUM)...say...
=SUM({2,0,12,20,0,0})
What must be preferred? One thing that is clear is that the comma version is not affected by text values or formula generated "" in the range/array/reference we want to conditionally sum.
When we need OR'ing, things get a bit complicated...
Let A2:B7 house the following sample...
{"a",5;"b",6;"a",4;"c",5;"a",7;"c",8},
E2 "a", and F2 "b".
[d]
=SUMPRODUCT(((A2:A7=E2)+(A2:A7=F2)),B2:B7)
would sum all values where A2:A7 houses either a value equal to E2 or to F2. The non-kosher version would be...
[e]
=SUMPRODUCT(((A2:A7=E2)+(A2:A7=F2))*B2:B7)
As I said above, [e] would give, rightly so, a #VALUE! error if B2:B7 contains any text value. It's normal to get such an error when we attempt a number with a text value. What is unusual is that [d], which obeys the syntax of the target function, is not affected in such cases.
Let E2 and F2 house "a" and "b", respectively.
The following formula, which we see very often,
[f]
=SUMPRODUCT((A2:A7={"a","b"})*B2:B7)
will also work, assuming there is no text value or "" in B2:B7.
{"a","b"} is what you get when select E2:F2 and apply F9 to the selection. This action shows you the "relation" or "correspondence" between a range and an array constant (This is apparently the reason why people often say "I have an array" instead of a range, while they mean the latter).
Note that {"a","b"} "corresponds" to a horizontal range, while {"a";"b"} to a vertical range.
Given the above correspondence between {"a","b"} and E2:F2, we can have
[g]
=SUMPRODUCT((A2:A7=B2:F2)*B2:B7)
which, I'm not surprised, IML noticed and inquired about. See
http://makeashorterlink.com/?V24535422
how non-obvious this formulation appears to be. In fact, what happens in [g] is a matrix manipulation (that is, why Beban is reminded of MMULT).
I'd like to add that the above also applies to formulas entered by using control+shift+enter.
HTH
Aladin
