You're welcome. Don't overlook Biff's improvement though. Using
ISNUMBER/MATCH is much easier to read, maintain, and will perform better
than stringing out 8 "OR"s.

Regarding OR and AND, the + and * operators act in this way in any
logical expression--this is not specific to SUMPRODUCT or any other
specific function.

For example,

=(1+1=1) + (1+1=2) yields 1 because it evaluates like
TRUE OR FALSE
TRUE

=(1+1=1) * (1+1=2) yields 0 because
TRUE AND FALSE
FALSE



Steve wrote:
Ok, back in the office this morning.... It works..... thank you.
So, + works as an OR operator, and * works as an AND operator with this
function. Interesting.
This is definitely something I'll be keeping handy for future use. There
have been many times when I'd wanted to do something like this but thought
I'd be headed for trouble, and wasn't sure which direction I SHOULD go with
it.

So, thank you very much.
Have a great day.


"smartin" wrote:

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.