Sumproduct clarification

  • Thread starter Thread starter Walter Mayes
  • Start date Start date
W

Walter Mayes

Various members of this forum have stated that the arrays used in
SUMPRODUCT must be the same.
Excel HELP says, under remarks, in part..."Array arguments must have the
same dimensions...."

In Cell C64, and dragged down to C104, I have played with the below
formulas and they both work

1. =SUMPRODUCT((a$18:a$58=a64)*b18:i18)
2. =SUMPRODUCT((a$18:a$58=a64)*b$18:i$58)

Column A is dates. B18 through I58 are numbers.

Arrays are NOT the same. Why do these formulas work?

Thanks
Walter Mayes
 
It is the array size that needs to be the same, not the contents of the ranges, eg:-

=SUMPRODUCT((A1:A20)*(A1:A25)) is not valid, whereas
=SUMPRODUCT((A1:A20)*(A1:A20)) or
=SUMPRODUCT((A1:A25)*(A1:A25)) are, pretty much irrespective of the contents.
 
Walter,

You only have one array in your sumproduct

(a$18:a$58=a64)*b18:i18 is a single array

(a$18:a$58=a64) will produce an array of 0 and 1

ie(1 0 0 1 1 0 1 1 1 0 0 0 1 0 1 0 0 0 1 1 0 1 1 0 1 1 1 0 0 1 1 1 1 0 0 1 0 0 1 0 1)

you then multiply each element (0 or 1) by the contents of B18 through I18

So if B18:I18 had (1 2 3 4 5 6 7 8)

You get an array like

1 2 3 4 5 6 7 8
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8
0 0 0 0 0 0 0 0
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
1 2 3 4 5 6 7 8
0 0 0 0 0 0 0 0
1 2 3 4 5 6 7 8
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8
0 0 0 0 0 0 0 0
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8
0 0 0 0 0 0 0 0
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
1 2 3 4 5 6 7 8
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
1 2 3 4 5 6 7 8
0 0 0 0 0 0 0 0
1 2 3 4 5 6 7 8

finally you'd take the sum of the above array (each element)

Dan E
 
Walter Mayes said:
Various members of this forum have stated that the arrays used in
SUMPRODUCT must be the same.
Excel HELP says, under remarks, in part..."Array arguments must have the
same dimensions...."

In Cell C64, and dragged down to C104, I have played with the below
formulas and they both work

1. =SUMPRODUCT((a$18:a$58=a64)*b18:i18)
2. =SUMPRODUCT((a$18:a$58=a64)*b$18:i$58)

Column A is dates. B18 through I58 are numbers.

Arrays are NOT the same. Why do these formulas work?

Thanks
Walter Mayes
Click to expand...

Help gives the syntax for SUMPRODUCT as:
SUMPRODUCT(array1,array2,array3, ...)
Note that the separators are commas.
In your formulas, you are explicitly multiplying two arrays, which gives a
single array. The SUMPRODUCT function only receives one array, so has no
'product' to calculate and so simply sums the elements of this single array.

This is not the typical way in which SUMPRODUCT is used (and is thus
described in this forum), which is to multiply two (or more) arrays element
by element, and then sum the results, such as:

1 4
2 5
3 6
which gives
4
10
18
which sums to 32

or
1 TRUE TRUE
2 TRUE FALSE
3 TRUE TRUE
which gives
1
0
3
which sums to 4.
 
As an example of how it works:-

The formula exploits the fact that Excel interprets TRUE as 1 and FALSE
as 0. Take the formula below:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20="A")*(C9:C20))

This sets up an array that gives you something that looks like this
(depending on the variables of course):-

A B C
9 TRUE * FALSE * 3
10 FALSE * FALSE * 4
11 TRUE * TRUE * 2
12 TRUE * TRUE * 1
13 TRUE * FALSE * 4
14 TRUE * TRUE * 3
15 TRUE * TRUE * 2
16 FALSE * TRUE * 8
17 TRUE * TRUE * 6
18 TRUE * TRUE * 8
19 TRUE * TRUE * 7
20 TRUE * TRUE * 6

Which because TRUE=1 and FALSE=0, is interpreted as:-

A B C
9 1 * 0 * 3 = 0
10 0 * 0 * 4 = 0
11 1 * 1 * 2 = 2
12 1 * 1 * 1 = 1
13 1 * 0 * 4 = 0
14 1 * 1 * 3 = 3
15 1 * 1 * 2 = 2
16 0 * 1 * 8 = 0
17 1 * 1 * 6 = 6
18 1 * 1 * 8 = 8
19 1 * 1 * 7 = 7
20 1 * 1 * 6 = 6
-------------
35

and the SUM bit just adds up all the end values of the products



If you exclude the last part of the formula, so that it becomes:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20="A"))

then what you end up with is a sum of a bunch of TRUE/FALSE values depending on whether or not the
criteria has been met on that row, and this is the same as counting the number of records that
meet your criteria. Imagine the above tables without Column C, and the last one would look like
the following:-

A B
9 1 * 0 = 0
10 0 * 0 = 0
11 1 * 1 = 1
12 1 * 1 = 1
13 1 * 0 = 0
14 1 * 1 = 1
15 1 * 1 = 1
16 0 * 1 = 0
17 1 * 1 = 1
18 1 * 1 = 1
19 1 * 1 = 1
20 1 * 1 = 1
 
Thanks to Ken, Dan and Paul. Guess I am confused on the terminology. Am
printing out all answers to digest and will do some more "studying".

THANKS
Walter Mayes
 
It can get a bit confusing, but is well worth persevering with trying to understand, as arrays are
a very powerful part of Excel's capability.

Chip Pearson has a nice intro to arrays on his site that will set you off in the right direction:-

http://cpearson.com/excel/array.htm
 
If I try SUMPRODUCT( (arrayA="x")*(arrayB)) with text in arrayA and numbers
in arrayB, I get N/A if the two arrays are not of equal dimensions.
Please tell how Help is wrong.
Bernard
 
Bernard,

If arrayA and arrayB are both column arrays you'll have
no luck, but if one were a row array, your outlook would
change...

ie.
ArrayA column (X Y W Y X Z) in A1:A6
ArrayB row (1 2 3) in B1:D1
=SUMPRODUCT((A1:A6="X")*B1:D1) returns 12
resultant 2D array from (A1:A6="X")*B1:D1
1 2 3
0 0 0
0 0 0
0 0 0
1 2 3
0 0 0
add 1+2+3+1+2+3 = 12
So sumproduct takes the sum of each individual component

If ArrayB were column (1 2 3) in B1:B3 you could produce
the same result with
{=SUMPRODUCT((A1:A6="X")*TRANSPOSE(B1:B3))}
array entered (control + shift + enter)

If you just use =SUMPRODUCT((A1:A6="X")*B1:B3)
it won't work because the resultant 2D array is
1 1 1
0 0 0
0 0 0
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A

Dan E
 
Try it with only the same row dimensions; e.g.:

put joe,bob,bob,bob,jim,mike in A1:A6;
put 1,2,3,4,5,6 in B1:B6;
put 7,8,9,10,11,12 in C1:C6;
put =SUMPRODUCT((A1:A6="bob")*(B1:C6)) in D1 to see it return 36, i.e.,
1*(2+3+4+8+9+10).

A1:A6 is 6x1; B1:C6 is 6x2.

Put 13,14,15,16,17,18 in D1:D6;
put =SUMPRODUCT((A1:A6="bob")*(B1:D6)) in E1 to see it return 81, i.e.,
1*(2+3+4+8+9+10+14+15+16).

A1:A6 is 6x1; B1:D6 is 6x3.

If, as the OP stated, "Excel HELP says . . . 'Array arguments must have
the same dimensions'", Excel HELP is wrong, as illustrated above.

Alan Beban
 
Alan -

by using the * operator, it still looks to me like your examples are
only passing a single array argument (i.e., the product of (A1:A6)
and (B1:C6)) to SUMPRODUCT().

When I use

=SUMPRODUCT(--(A1:A6="bob"),(B1:C6))

I get the #VALUE! error, just as Help says.

I just don't see where Help is wrong...
 
I guess we're having a semantic problem. The original OP was
interpreting the HELP to say that in his illustrations,
1. =SUMPRODUCT((a$18:a$58=a64)*b18:i18)
2. =SUMPRODUCT((a$18:a$58=a64)*b$18:i$58)

a$18:a$58 and b18:i18 had to be the same dimensions, and a$18:a$58 and
b18:i58 had to be the same dimensions.

In his post, Bernard Liengme, with his illustration

SUMPRODUCT( (arrayA="x")*(arrayB))

seemed to be taking the same posture, i.e., that HELP required arrayA
and arrayB (with the * operator) to be the same dimensions; that
otherwise he got an error message.

That's what my illustrations were responding to.

What you're suggesting is that that isn't what HELP says.
 
Alan

Your interpretation below is correct, from my point of view. I had asked
a question over a year ago and the SUMPRODUCT function was given to me as a
solution to my question and it worked perfectly. I needed information from
two columns and the column "ranges" had to match. Therefore I assumed I
could not use SUMPRODUCT with columns AND rows and never tried it.
My understanding was that (a18:a58) would be 1 "array" and (b18:b58)
would be the second "array", so therefore (a18:a58) AND (b18:i18) would not
work. (Not the same dimension) But when I recently tried it, just for the
h--- of it, it worked. Now I am really confused. Apparently (a18:a58) is NOT
the "array" but merely a "range" and if "that" is the case....what is the
"array"? Am I making any sense?

Walter Mayes
 
Both A18:A58 and B18:I18 are ranges, and for the purposes of
SUMPRODUCT() arrays.

However, they cannot be individually passed to SUMPRODUCT as arrays
because they are not the same size. Instead, by using

=SUMPRODUCT((A18:A58) * (B18:*I58))

you are, first, array multiplying the ranges to get a 41x8 array:

{A18*B18,...,A58*B58;A18*C18,...A18*C58;...(A*D)...;...(A*E)...;...(A
*F)...;...(A*G)...;...(A*H)...;A18*I18,...,A57*I57, A58*I58}

then passing that single array to SUMPRODUCT().

In other words, * is not an argument separator, it is an operator,
and the operation takes precedence over the function call.

As further evidence, create two numeric column arrays and enter both

=SUMPRODUCT(A1:A100,B1:B100)

and

=SUMPRODUCT(A1:A100*B1:B100)

now enter a text value in A1. SUMPRODUCT's Help file says SUMPRODUCT
treats text as 0. The first formula does. The latter formula doesn't.



then passing that arrayIn article <[email protected]>,
 
Precisely. Nowhere in Help for SUMPRODUCT does it mention the construction
SUMPRODUCT(array1*array2).
It speaks of
SUMPRODUCT(array1,array2).
These two are quite distinct.
It's so easy to read into a description what you are expecting to find.
Whilst I have several criticisms of Help in general, in this instance what
it says is correct. It could say more, of course, and this would be helpful,
but that's not the same as saying it's wrong.
 
Paul said:
Precisely. Nowhere in Help for SUMPRODUCT does it mention the construction
SUMPRODUCT(array1*array2).
It speaks of
SUMPRODUCT(array1,array2).
These two are quite distinct.
Click to expand...

Yes. The former, single argument form is one of the few really useful
features Microsoft inadvertently allowed into Excel. Inadvertent? Look at
online help: given the typographic conventions, a second argument appears to
be required. Thankfully it isn't.

To cynics like me, it's a good thing the single argument form doesn't appear
in online help. If the programmers or the project managers had been aware
that they were adding anything really useful to Excel, they would have
'fixed' it to prevent unreasonable expecations of functionality from
developing among Excel's user base.
 
Back
Top