Steve wrote:
Howdee all.
I'm trying something new with sumproduct that I've always been too
intimidated to try-- a 10 array sumproduct function.
My goal is to tally the totals of 8 different values, with one common value
between them.
I set my common arrary 1st-- (E6:E266=E139)
I then select my subsequent 8 arrays. Each one having a different criteria--
(A6:A266=Y18)
(A6:A266=Y19)
(A6:A266=Y20)
(A6:A266=Y21)
(A6:A266=Y22)
(A6:A266=Y23)
(A6:A266=Y24)
(A6:A266=Y25)
For each of these, I anticipate no more than two true responses.
I then have my final array
(b6:b266)
This last array has my values that I want summed.
Now, as I understand sumproduct, the true response will return a 1, and
false, a 0.
As my worksheet is ordered, with my initial array, I get 8 true responses.
With my second array- I get two trues. 3rd- two trues; 4th, one, and on out
to the 8th basic array.

For a reason that I'm not clear on, it appears that if the true response is
not in the same position as the previous array's true response, it returns a
false-- which is 0 (thereby nullifying the entire response to 0).

[snipped]

Hi Steve,

I think you want something like this:

=SUMPRODUCT((E6:E266=E139)*((A6:A266=Y18)+(A6:A266 =Y19)+(A6:A266=Y20)+...)*(b6:b266))

You are missing an important part of how arrays work. If you multiply
all the arrays, you will most likely end up with zero if some of the
conditions are mutually exclusive. I believe this is your problem.

In the formula above I placed what I understand to be your mutually
exclusive conditions in an OR clause (note the + signs and extra paren
grouping). Know that "+" acts like "OR" and "*" acts like "AND" when
dealing with logical expressions.

If this doesn't make sense, let us know. However, I suggest you set up a
very small test of 5 rows and simulate your conditions. I can provide
one if needed. Use the formula auditing tool "evaluate formula" to watch
how the arrays are evaluated in SUMPRODUCT. This can be instrumental in
understanding how all these concepts work.