SUMPRODUCT

  • Thread starter Thread starter FLKulchar
  • Start date Start date
F

FLKulchar

Boy...do I need help with this formula.

For example, what does the following mean:

=SUMPRODUCT (0+ISNA(MATCH(A1:A10,{2,4},0)),A1:A10)

I know it means to add the column of numbers omitting
those that are 2 or 4..

but HOW DOES THIS "MAGICAL" SUMPRODUCT formula WORK??

Thanks,

Larry
 
FLKulchar said:
Boy...do I need help with this formula.

For example, what does the following mean:

=SUMPRODUCT (0+ISNA(MATCH(A1:A10,{2,4},0)),A1:A10)

I know it means to add the column of numbers omitting
those that are 2 or 4..

but HOW DOES THIS "MAGICAL" SUMPRODUCT formula WORK??

Thanks,

Larry
Click to expand...

As usual, it is easiest to work from the middle outwards.

MATCH(A1:A10,{2,4},0) looks for exact matches between each of the the cells
A1:A10 in turn and the array {2,4}. It produces an array of 10 answers. The
first says if there is a match between A1 and the array {2,4}. If a match is
found, the answer is the position of the match in the array (i.e. 1 if the
match is with 2, or 2 if the match is with 4). If no match is found the
answer is #N/A. Similarly for A2:A10. The important point is that the array
of 10 answers has a number where a match is found and #N/A otherwise.

This array is passed to ISNA, which simply converts #N/A to TRUE and numbers
to FALSE. So we now have an array of 10 boolean values where 'matches' are
indicated by FALSE.

Adding 0 to (each element of) this array is simply a way of converting TRUE
to 1 and FALSE to 0. Another way would have been to multiply the array by 1.
So we now have an array of 10 ones or zeros where 'matches' are indicated by
zero.

SUMPRODUCT then multiplies each element of this array with each of A1:A10
and sums the results. Hence we end up with the sum of A1:A10 but excluding
2's or 4's.
 
Hi Larry
1. Lets assume you have the following data in A1:A10
1
2
3
4
5
6
7
8
9
10

2. The formula ISNA(MATCH(A1:A10,{2,4},0)) will return TRUE for all
values in A1:A10 if they are not found by the MATCH function (in your
example for all values besides 2 or 4). By adding a '0' to this part,
Excel converts this TRUE or FALSE value to '1' or '0'. Excel has now
createx a matrix of 0 and 1 in respect to your values in A1:A10:
1
0
1
0
1
1
1
1
1
1

3. Excel will then multiply this mtraix of 0 and 1 with the values in
A1:A10 and add the results (this is what SUMPRODUCT does) so
1*1
0*2
1*3
0*4
1*5
1*6
1*7
1*8
1*9
1*10

= 49

So SUMPRODUCT will add all values in A1:A10 besides 2 and 4

Have also a look at
http://www.j-walk.com/ss/excel/tips/tip74.htm
for more examples

HTH
Frank
 
Back
Top