multi-array sumproduct

[Steve]

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).

I get this in smaller arrays, as I do a 3 criteria array sumproduct all the
time.
It was my hope to have a true for array 1, and 2. A true for array 1 and 3,
a true for array 1 and 4, etc.... through 1 and 8. However, the true
responses are in a different position for each of the secondary 8 arrays.

E.g., let's say that 1 and 2 have a true response at position 30.
1 and 3 have it at pos'n 42, 1 and 4 are at pos'n 45, 1 and 5 are at pos'n
53, 1 & 6 at 156, 1 & 7 at 232, 1 & 8 at 245, and 1 & 9 are at 248.

It appears that even if array 1 and 2 is true, but array 1 & 3 is false at
the identical position of 42-- even though its true at 45, it nullifies the
function.


As I write this, I'm beginning to think that I'd be better off either
nesting my sumproduct (SP) functions, or doing 8 individual SP eq's.

Could someone help clarify this for me?

Thank you.

 

[Steve]

Hi,
Thanks for the response.
So, what I want to do is to sum each of the arrays instead of multiplying
them. That makes sense.
I'll try that in the morning to see how it works.
Thank you.

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.

 

[T. Valko]

....((A6:A266=Y18)+(A6:A266=Y19)+(A6:A266=Y20)+...)...

Instead of doing that for 8 arrays it'd be better to use:

--(ISNUMBER(MATCH(A6:A266,Y18:Y25,0)))

--
Biff
Microsoft Excel MVP

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.

 

[Steve]

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.

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.

 

[Steve]

Hi Biff,
Thank you for the response.
I wasn't trying to determine IF there was an instance of the elements,
rather I actually needed a tally of the values that matched the specific
criteria required-- hence the 8 arrays in the middle.

I did try yours, and it came back as a false-- which after having found my
original 10 array function went to zero, makes sense.

The values of each true aren't in the same position within each array. As
I've considered that further, the reason my original use-- in times past-- of
Sumproduct worked is that the true responses are true for the same position
within each array.
(too bad I didn't grasp that when I was taking linear algebra... it would've
helped a lot....)

I had never thought of that this far out before yesterday's use.
So, while yours didn't actually "solve" my issue, it did allow me to
recognize a little more of the logic involved.
Thanks again for your help.
Best.

....((A6:A266=Y18)+(A6:A266=Y19)+(A6:A266=Y20)+...)...

Instead of doing that for 8 arrays it'd be better to use:

--(ISNUMBER(MATCH(A6:A266,Y18:Y25,0)))

--
Biff
Microsoft Excel MVP

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.

 

[T. Valko]

Don't know why it didn't work for you.

Consider this simple example:

...........A..........B..........C
1......Yes.........x..........1
2......Yes.........a..........1
3......No..........y..........1
4......No..........b..........1
5......Yes.........z..........1

Sum C1:C5 where A1:A5 = Yes and B1:B5 = x or y or z.

Criteria:

E1 = Yes
F1 = x
F2 = y
F3 = z

Both of these formulas do just that:

=SUMPRODUCT((A1:A5=E1)*((B1:B5=F1)+(B1:B5=F2)+(B1:B5=F3))*(C1:C5))

=SUMPRODUCT(--(A1:A5=E1),--(ISNUMBER(MATCH(B1:B5,F1:F3,0))),C1:C5)

The ISNUMBER(MATCH(...)) version is the better choice. Especially if you
wanted to test for 8 "or" conditions in B1:B5 (as your original post
described).

--
Biff
Microsoft Excel MVP

Hi Biff,
Thank you for the response.
I wasn't trying to determine IF there was an instance of the elements,
rather I actually needed a tally of the values that matched the specific
criteria required-- hence the 8 arrays in the middle.

I did try yours, and it came back as a false-- which after having found my
original 10 array function went to zero, makes sense.

The values of each true aren't in the same position within each array. As
I've considered that further, the reason my original use-- in times past--
of
Sumproduct worked is that the true responses are true for the same
position
within each array.
(too bad I didn't grasp that when I was taking linear algebra... it
would've
helped a lot....)

I had never thought of that this far out before yesterday's use.
So, while yours didn't actually "solve" my issue, it did allow me to
recognize a little more of the logic involved.
Thanks again for your help.
Best.

....((A6:A266=Y18)+(A6:A266=Y19)+(A6:A266=Y20)+...)...

Instead of doing that for 8 arrays it'd be better to use:

--(ISNUMBER(MATCH(A6:A266,Y18:Y25,0)))

--
Biff
Microsoft Excel MVP

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.

 

[Herbert Seidenberg]

Excel 2007 Table
With Structured References
Full example:
http://www.mediafire.com/file/oroorfnmtjz/07_29_09.xlsx