Hi,

Does anyone know if SUMPRODUCT can be used as an array formula?
If this is the case, what is the syntax and can the syntax include conditions?

If it works, I would imagine that the syntax looks something like this:

{=SUMPRODUCT(IF(logical_tests,value_if_true,""),IF (logical_tests_value_if_true,""))}

However, if SUMPRODUCT doesn't work like an array formula, I don't want to
waste time trying to figure it out. Please let me know if you have any
experience with this.

Thanks,
Henrik

see

http://tinyurl.com/7sqmu

Henrik wrote:
Hi,

Does anyone know if SUMPRODUCT can be used as an array formula?
If this is the case, what is the syntax and can the syntax include conditions?

SumProduct always operates on (computed) arrays. That is the reason why
there is ordinarily no need to confirm SumProduct formulas with
control+shift+enter as you would the formulas which are often referred
to as array formulas.


If it works, I would imagine that the syntax looks something like this:

{=SUMPRODUCT(IF(logical_tests,value_if_true,""),IF (logical_tests_value_if_true,""))}


When an IF() function call is a part of a formula and it must return a
computed array, the formula it's part of must be confirmed with
control+shift+enter, even when wrapped inside the SumProduct function.

Suppose we have:

FL Yes
FL Yes
FL No
GA Yes
GA No
LA No
MD Yes

in A2:B8 and we want to count records consisting of "FL" and "Yes".

Required counting can be effected in a number of ways. Restricting the
choice set to setups in terms of a single formula, we can have:

1.

{=SUM(IF(A2:A8="FL",IF(B2:B8="Yes",1,0)))}

2.

{=COUNT(IF(A2:A8="FL",IF(B2:B8="Yes",1)))}

3.

=SUMPRODUCT(--(A2:A8="FL"),--(B2:B8="Yes"))

4.

=DCOUNTA(A1:B8,1,L1:M2)

where A1:B1 houses labels and L1:M2 the appropriate criteria.

5. The following formula

{=SUMPRODUCT(IF(A2:A8="FL",IF(B2:B8="Yes",1,0)))}

would also work as intended. However, the issue is whether one would
want opt for (5) when (3) is available. When an array returning IF()
call cannot be eliminated, it's better (an elegant action indeed) not to
wrap such a call into a SumProduct and invoke instead an "array
formula", that is, a formula that must be confirmed with
control+shift+enter.

[...]

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.