sumproduct arrays

[dakotasteve]

Hi Everyone!
I have confirmed manually that my sumproduct formula only sums on
program of my array {2010,2030,2050} it ignores 2030, and 2050. Is i
because I also sum an array of fund numbers-i.e. {100,207 &270}? Do
violate something by summing on two multiple arrays in one formula.

The formula I am using is:
=SUMPRODUCT((EXPFD04={100,207,270})*(EXP04PROGRAM={2010,2030,2050})*(EXP04ACTUALS))

Thanks Everyone!
Steve in Los Gatos CA :

 

[Bob Phillips]

Steve,

It works fine for me. I just put these values in

A B C
1 100 2010 1
2 207 2030 2
3 270 2050 3


and I got 6 as expected

 

[Aladin Akyurek]

All named ranges must be of the same size, assuming that they are vectors,
not multicolumn matrices.
The vectors/ranges of interest must not house text-formatted numbers. If
2030 is a text-formatted number in EXPFD04, the formula ignore that entry.
To make sure that they are all true numbers, select an unused cell, run
Edit|Copy, select EXPFD04, and run Edit|Paste Special with the Add option
checked. Apply the same procedure to other ranges.

BTW, it's faster to use:

=SUMPRODUCT(--ISNUMBER(MATCH(EXPFD04,{100,207,270},0)),--ISNUMBER(MATCH(EXP04PROGRAM,{2010,2030,2050},0)),EXP04ACTUALS)