It's the delimiter being used. You have a ; in there as opposed to my ,
Switching them works fine for me. If I put a ; in there it seems to try and
evaluate the range against what is effectively a range within the braces, ie
{"Email";"Fax","Phone"}
With the following data:-
A B
1 1 Email
2 1 Fax
3 1 Phone
=SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Email";"Fax";"Phone"})) is effectively
doing:-
A B
1 =1 (yes) * ={Email (yes) = 1
2 =1 (yes) * =Fax (yes) = 1
3 =1 (yes) * =Phone} (yes) = 1
----------------------------------------------------
SUM = 3
evaluating the data within the braces as though it were a range, just like the
A1:A3 or B1:B3, which because it happens to have the same amount of arguments as
there are rows will give you effectively an equal length range, so the formula
appears to work, although if you switch the arguments around you will see in
fact that it is not giving you what you want, eg:-
=SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Phone";"Fax";"Email"})) gives you:-
A B
1 =1 (yes) * ={Email (no) = 0
2 =1 (yes) * =Fax (yes) = 1
3 =1 (yes) * =Phone} (no) = 0
------------------------------------------
SUM = 1
and so because there are only 3 arguments, if you increase the ranges to 4 rows,
there is as far as the formula is concerned a range of unequal length in the
formula:-
=SUMPRODUCT(($A$1:$A$4=1)*($B$1:$B$4={"Email";"Fax";"Phone"})) is effectively
doing:-
A B
1 =1 (yes) * ={Email (yes) = 1
2 =1 (yes) * =Fax (yes) = 1
3 =1 (yes) * =Phone (yes) = 1
4 =1 (yes) * =#N/A } (oops) =#N/A
----------------------------------------------
SUM = #N/A
whereas you really want the formula to evaluate each row in Col B for all the
options in the braces, so using a comma as delimiter will make the arguments a
horizontal array of data and evaluate each row against each item within the
array, eg:-
A B
1 =1 * ={Email or Fax or Phone} (yes) = 1
2 =1 * ={Email or Fax or Phone} (yes) = 1
3 =1 * ={Email or Fax or Phone} (yes) = 1
4 =1 * ={Email or Fax or Phone} (yes) = 1
-------------------------------------
SUM = 4
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03
-------------------------------------------------------------------------- --
It's easier to beg forgiveness than ask permission
-------------------------------------------------------------------------- --