Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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:A2000<List!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? |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Try this:
=SUMPRODUCT(--(ISNA(MATCH(List!A2:A10,Data!A2:A2000,0))) Biff "~L" wrote in message ... 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:A2000<List!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? |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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). |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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). |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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). |
#6
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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). |
#7
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
SUMPRODUCT + CSE | Excel Worksheet Functions | |||
How to make array of noncontig cells for SUMPRODUCT? | Excel Discussion (Misc queries) | |||
defintion of array function | Excel Discussion (Misc queries) | |||
Array | Excel Worksheet Functions | |||
sumproduct , array or countif? | Excel Worksheet Functions |