Sumproduct Excluding Array

Hi,

I have a list of names and a list of names with a list of values and I'm
using Sumproduct to compare the two and give the sum of the values for each
name.

But one of the names on the comparison list is 'other' which is meant to
include all names not on the list.

When I add *--(Data!A2:A2000List!A2:A10) it doesn't work because the
arrays aer not the same size. If I make them the same size, or if I list out
the names as in *--(Data!A2:A2000{"Name1","Name2","Name3"}) the expected
value is not returned.

It seems I'm not asking Excel for what I think I'm it asking for.

How do I get the sum of the values for all names not on my list?

Try this:

=SUMPRODUCT(--(ISNA(MATCH(List!A2:A10,Data!A2:A2000,0)))

Biff

"~L" wrote in message
news
Hi,

I have a list of names and a list of names with a list of values and I'm
using Sumproduct to compare the two and give the sum of the values for
each
name.

But one of the names on the comparison list is 'other' which is meant to
include all names not on the list.

When I add *--(Data!A2:A2000List!A2:A10) it doesn't work because the
arrays aer not the same size. If I make them the same size, or if I list
out
the names as in *--(Data!A2:A2000{"Name1","Name2","Name3"}) the expected
value is not returned.

It seems I'm not asking Excel for what I think I'm it asking for.

How do I get the sum of the values for all names not on my list?

By itself, it returned 1 (The match with 'other' was NA).
When added in to the expanded formula:
=SUMPRODUCT(--(Data!$B$2:$B$2000)*--(ISNA(MATCH(List!$A$2:$A$10,Data!$A$2:$A$2000,0))) )
It returned NA because A2:A10 is not the same size as the other arrays.
When A2:A10 is expanded, the formula returns a number that is higher than the
expected result (the check for this was to sum the totals on the list page,
then sum the values on the data page, since it is hopefully sorting out
numbers by names, the results should be equal but were not).

First,

.....2000)*--(ISNA(MATCH....

Get rid of the "*". Replace it with a comma.

...........A............B...........C
1......Tom........Tom........10
2......Bob.........Bob........10
3.....Other........Sam........10
4.....................Sue..........10
5.....................Tim..........10

=SUMPRODUCT(--(ISNA(MATCH(B1:B5,A1:A3,0))),C1:C5)

Returns 30. Sums the values for Sam, Sue and Tim.

Biff

"~L" wrote in message
...
By itself, it returned 1 (The match with 'other' was NA).
When added in to the expanded formula:
=SUMPRODUCT(--(Data!$B$2:$B$2000)*--(ISNA(MATCH(List!$A$2:$A$10,Data!$A$2:$A$2000,0))) )
It returned NA because A2:A10 is not the same size as the other arrays.
When A2:A10 is expanded, the formula returns a number that is higher than
the
expected result (the check for this was to sum the totals on the list
page,
then sum the values on the data page, since it is hopefully sorting out
numbers by names, the results should be equal but were not).

Not sure if this is what you're looking for or not, or if you need to have
everything in one cell, per se.

On List sheet, in B2, enter:

=sumif(Data!A$2:A$2000,B2,Data!B$2:B$2000)

Fill this down to B10. Then, in B11, enter

=sum(Data!B2:B2000)-sum(A2:A10)

That will back into the number you want, by totalling the whole thing and
then removing the amounts that correspond to the listed names. Not nearly as
elegant as what you're describing, but ought to give the right result if it
fits into space constraints.

Good luck.
--
Mike Lee
McKinney,TX USA


"~L" wrote:

By itself, it returned 1 (The match with 'other' was NA).
When added in to the expanded formula:
=SUMPRODUCT(--(Data!$B$2:$B$2000)*--(ISNA(MATCH(List!$A$2:$A$10,Data!$A$2:$A$2000,0))) )
It returned NA because A2:A10 is not the same size as the other arrays.
When A2:A10 is expanded, the formula returns a number that is higher than the
expected result (the check for this was to sum the totals on the list page,
then sum the values on the data page, since it is hopefully sorting out
numbers by names, the results should be equal but were not).

Perfect!

I was able to work with that to get the expected result.

Thanks!

"T. Valko" wrote:

First,

.....2000)*--(ISNA(MATCH....

Get rid of the "*". Replace it with a comma.

...........A............B...........C
1......Tom........Tom........10
2......Bob.........Bob........10
3.....Other........Sam........10
4.....................Sue..........10
5.....................Tim..........10

=SUMPRODUCT(--(ISNA(MATCH(B1:B5,A1:A3,0))),C1:C5)

Returns 30. Sums the values for Sam, Sue and Tim.

Biff

"~L" wrote in message
...
By itself, it returned 1 (The match with 'other' was NA).
When added in to the expanded formula:
=SUMPRODUCT(--(Data!$B$2:$B$2000)*--(ISNA(MATCH(List!$A$2:$A$10,Data!$A$2:$A$2000,0))) )
It returned NA because A2:A10 is not the same size as the other arrays.
When A2:A10 is expanded, the formula returns a number that is higher than
the
expected result (the check for this was to sum the totals on the list
page,
then sum the values on the data page, since it is hopefully sorting out
numbers by names, the results should be equal but were not).

You're welcome. Glad we got that straightened out! I think I misunderstood
your original intention.

Biff

"~L" wrote in message
...
Perfect!

I was able to work with that to get the expected result.

Thanks!

"T. Valko" wrote:

First,

.....2000)*--(ISNA(MATCH....

Get rid of the "*". Replace it with a comma.

...........A............B...........C
1......Tom........Tom........10
2......Bob.........Bob........10
3.....Other........Sam........10
4.....................Sue..........10
5.....................Tim..........10

=SUMPRODUCT(--(ISNA(MATCH(B1:B5,A1:A3,0))),C1:C5)

Returns 30. Sums the values for Sam, Sue and Tim.

Biff

"~L" wrote in message
...
By itself, it returned 1 (The match with 'other' was NA).
When added in to the expanded formula:
=SUMPRODUCT(--(Data!$B$2:$B$2000)*--(ISNA(MATCH(List!$A$2:$A$10,Data!$A$2:$A$2000,0))) )
It returned NA because A2:A10 is not the same size as the other arrays.
When A2:A10 is expanded, the formula returns a number that is higher
than
the
expected result (the check for this was to sum the totals on the list
page,
then sum the values on the data page, since it is hopefully sorting out
numbers by names, the results should be equal but were not).