|
SUMPRODUCT
Greetings,
Im looking for an explanation for something happening in Excel that I dont understand. In a worksheet cell I have a formula =SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=Asia),--([Data.xls]A!$AN$2:$AN$5000)) which works fine. Regardless of how I try to do it, I cant copy it to any cell on any other worksheet without an error #VALUE! rising from the --([Data.xls]A!$AN$2:$AN$5000)) array. I dont understand why I get an error when I know the formula works fine. Appreciate whatever help you can give me! Jim |
I tried it in XL2K but could not reproduce the error.
Alok Joshi "Jimbo" wrote: Greetings, Im looking for an explanation for something happening in Excel that I dont understand. In a worksheet cell I have a formula =SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=Asia),--([Data.xls]A!$AN$2:$AN$5000)) which works fine. Regardless of how I try to do it, I cant copy it to any cell on any other worksheet without an error #VALUE! rising from the --([Data.xls]A!$AN$2:$AN$5000)) array. I dont understand why I get an error when I know the formula works fine. Appreciate whatever help you can give me! Jim |
is it just the AN column array you are having problems with, or the full
equation? try a =sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000)) if you get a 1 try =sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000),row(([Data.xls]A!$AN$2:$AN$5000)) if you get more that 1 try reducing the range until you do get 1 "Jimbo" wrote: Greetings, Im looking for an explanation for something happening in Excel that I dont understand. In a worksheet cell I have a formula =SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=Asia),--([Data.xls]A!$AN$2:$AN$5000)) which works fine. Regardless of how I try to do it, I cant copy it to any cell on any other worksheet without an error #VALUE! rising from the --([Data.xls]A!$AN$2:$AN$5000)) array. I dont understand why I get an error when I know the formula works fine. Appreciate whatever help you can give me! Jim |
Thanks bj,
What I get with both of these is"0". Jim "bj" wrote: is it just the AN column array you are having problems with, or the full equation? try a =sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000)) if you get a 1 try =sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000),row(([Data.xls]A!$AN$2:$AN$5000)) if you get more that 1 try reducing the range until you do get 1 "Jimbo" wrote: Greetings, Im looking for an explanation for something happening in Excel that I dont understand. In a worksheet cell I have a formula =SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=Asia),--([Data.xls]A!$AN$2:$AN$5000)) which works fine. Regardless of how I try to do it, I cant copy it to any cell on any other worksheet without an error #VALUE! rising from the --([Data.xls]A!$AN$2:$AN$5000)) array. I dont understand why I get an error when I know the formula works fine. Appreciate whatever help you can give me! Jim |
what do you get with just
=sumproduct(--([Data.xls]A!$AN$2:$AN$5000)) and =sumproduct(--([Data.xls]A!$BN$2:$BN$5000)) and =sumproduct((--([Data.xls]A!$AN$2:$AN$5000),--([Data.xls]A!$BN$2:$BN$5000)) and =SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=Asia),--([Data.xls]A!$BN$2:$BN$5000)) assuming BN is blank the reason I am asking is that the only ways I know to get an "#value with sumproduct is to have a #Value error in one of the cells or when there are unequal arrays. "Jimbo" wrote: Thanks bj, What I get with both of these is"0". Jim "bj" wrote: is it just the AN column array you are having problems with, or the full equation? try a =sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000)) if you get a 1 try =sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000),row(([Data.xls]A!$AN$2:$AN$5000)) if you get more that 1 try reducing the range until you do get 1 "Jimbo" wrote: Greetings, Im looking for an explanation for something happening in Excel that I dont understand. In a worksheet cell I have a formula =SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=Asia),--([Data.xls]A!$AN$2:$AN$5000)) which works fine. Regardless of how I try to do it, I cant copy it to any cell on any other worksheet without an error #VALUE! rising from the --([Data.xls]A!$AN$2:$AN$5000)) array. I dont understand why I get an error when I know the formula works fine. Appreciate whatever help you can give me! Jim |
I tried these as with the following results on the original sheet in my
workbook: 75 0 0 0 You are correct in that there is no data in BN. Next I copied all four of these cells to another worksheet and in that sheet the results a "#VALUE!" 0 "VALUE!" 0 At least it's consistent, but not as expected. "bj" wrote: what do you get with just =sumproduct(--([Data.xls]A!$AN$2:$AN$5000)) and =sumproduct(--([Data.xls]A!$BN$2:$BN$5000)) and =sumproduct((--([Data.xls]A!$AN$2:$AN$5000),--([Data.xls]A!$BN$2:$BN$5000)) and =SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=Asia),--([Data.xls]A!$BN$2:$BN$5000)) assuming BN is blank the reason I am asking is that the only ways I know to get an "#value with sumproduct is to have a #Value error in one of the cells or when there are unequal arrays. "Jimbo" wrote: Thanks bj, What I get with both of these is"0". Jim "bj" wrote: is it just the AN column array you are having problems with, or the full equation? try a =sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000)) if you get a 1 try =sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000),row(([Data.xls]A!$AN$2:$AN$5000)) if you get more that 1 try reducing the range until you do get 1 "Jimbo" wrote: Greetings, Im looking for an explanation for something happening in Excel that I dont understand. In a worksheet cell I have a formula =SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=Asia),--([Data.xls]A!$AN$2:$AN$5000)) which works fine. Regardless of how I try to do it, I cant copy it to any cell on any other worksheet without an error #VALUE! rising from the --([Data.xls]A!$AN$2:$AN$5000)) array. I dont understand why I get an error when I know the formula works fine. Appreciate whatever help you can give me! Jim |
this problem has succeeded is confusing me. (not really that hard to do)
Is the 75 the expected number for the sum of the AN column? I have no clue why you would get a "Value" for the data copied to another sheet. "Jimbo" wrote: I tried these as with the following results on the original sheet in my workbook: 75 0 0 0 You are correct in that there is no data in BN. Next I copied all four of these cells to another worksheet and in that sheet the results a "#VALUE!" 0 "VALUE!" 0 At least it's consistent, but not as expected. "bj" wrote: what do you get with just =sumproduct(--([Data.xls]A!$AN$2:$AN$5000)) and =sumproduct(--([Data.xls]A!$BN$2:$BN$5000)) and =sumproduct((--([Data.xls]A!$AN$2:$AN$5000),--([Data.xls]A!$BN$2:$BN$5000)) and =SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=Asia),--([Data.xls]A!$BN$2:$BN$5000)) assuming BN is blank the reason I am asking is that the only ways I know to get an "#value with sumproduct is to have a #Value error in one of the cells or when there are unequal arrays. "Jimbo" wrote: Thanks bj, What I get with both of these is"0". Jim "bj" wrote: is it just the AN column array you are having problems with, or the full equation? try a =sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000)) if you get a 1 try =sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000),row(([Data.xls]A!$AN$2:$AN$5000)) if you get more that 1 try reducing the range until you do get 1 "Jimbo" wrote: Greetings, Im looking for an explanation for something happening in Excel that I dont understand. In a worksheet cell I have a formula =SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=Asia),--([Data.xls]A!$AN$2:$AN$5000)) which works fine. Regardless of how I try to do it, I cant copy it to any cell on any other worksheet without an error #VALUE! rising from the --([Data.xls]A!$AN$2:$AN$5000)) array. I dont understand why I get an error when I know the formula works fine. Appreciate whatever help you can give me! Jim |
The confusion is the reason I initiated this inquiry - I've copied lots of
formulas to different sheets and workbooks for that matter, but have never encountered a problem like this before. I'm in the same boat as you as what I'm seeing makes no sense to me and I have no clue as to what is wrong. In response to your question, I confirm that the correct sum for column AN is 75. Perhaps my only recourse is to trash the whole workbook and start over, but I was hoping I could avoid doing that. "bj" wrote: this problem has succeeded is confusing me. (not really that hard to do) Is the 75 the expected number for the sum of the AN column? I have no clue why you would get a "Value" for the data copied to another sheet. "Jimbo" wrote: I tried these as with the following results on the original sheet in my workbook: 75 0 0 0 You are correct in that there is no data in BN. Next I copied all four of these cells to another worksheet and in that sheet the results a "#VALUE!" 0 "VALUE!" 0 At least it's consistent, but not as expected. "bj" wrote: what do you get with just =sumproduct(--([Data.xls]A!$AN$2:$AN$5000)) and =sumproduct(--([Data.xls]A!$BN$2:$BN$5000)) and =sumproduct((--([Data.xls]A!$AN$2:$AN$5000),--([Data.xls]A!$BN$2:$BN$5000)) and =SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=Asia),--([Data.xls]A!$BN$2:$BN$5000)) assuming BN is blank the reason I am asking is that the only ways I know to get an "#value with sumproduct is to have a #Value error in one of the cells or when there are unequal arrays. "Jimbo" wrote: Thanks bj, What I get with both of these is"0". Jim "bj" wrote: is it just the AN column array you are having problems with, or the full equation? try a =sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000)) if you get a 1 try =sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000),row(([Data.xls]A!$AN$2:$AN$5000)) if you get more that 1 try reducing the range until you do get 1 "Jimbo" wrote: Greetings, Im looking for an explanation for something happening in Excel that I dont understand. In a worksheet cell I have a formula =SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=Asia),--([Data.xls]A!$AN$2:$AN$5000)) which works fine. Regardless of how I try to do it, I cant copy it to any cell on any other worksheet without an error #VALUE! rising from the --([Data.xls]A!$AN$2:$AN$5000)) array. I dont understand why I get an error when I know the formula works fine. Appreciate whatever help you can give me! Jim |
It does sound as though you may have a corrupted file. There may be work
arounds, but my experience has been when a file gets corrupted, it often quickly gets worse. "Jimbo" wrote: The confusion is the reason I initiated this inquiry - I've copied lots of formulas to different sheets and workbooks for that matter, but have never encountered a problem like this before. I'm in the same boat as you as what I'm seeing makes no sense to me and I have no clue as to what is wrong. In response to your question, I confirm that the correct sum for column AN is 75. Perhaps my only recourse is to trash the whole workbook and start over, but I was hoping I could avoid doing that. "bj" wrote: this problem has succeeded is confusing me. (not really that hard to do) Is the 75 the expected number for the sum of the AN column? I have no clue why you would get a "Value" for the data copied to another sheet. "Jimbo" wrote: I tried these as with the following results on the original sheet in my workbook: 75 0 0 0 You are correct in that there is no data in BN. Next I copied all four of these cells to another worksheet and in that sheet the results a "#VALUE!" 0 "VALUE!" 0 At least it's consistent, but not as expected. "bj" wrote: what do you get with just =sumproduct(--([Data.xls]A!$AN$2:$AN$5000)) and =sumproduct(--([Data.xls]A!$BN$2:$BN$5000)) and =sumproduct((--([Data.xls]A!$AN$2:$AN$5000),--([Data.xls]A!$BN$2:$BN$5000)) and =SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=Asia),--([Data.xls]A!$BN$2:$BN$5000)) assuming BN is blank the reason I am asking is that the only ways I know to get an "#value with sumproduct is to have a #Value error in one of the cells or when there are unequal arrays. "Jimbo" wrote: Thanks bj, What I get with both of these is"0". Jim "bj" wrote: is it just the AN column array you are having problems with, or the full equation? try a =sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000)) if you get a 1 try =sumproduct(--iserror([Data.xls]A!$AN$2:$AN$5000),row(([Data.xls]A!$AN$2:$AN$5000)) if you get more that 1 try reducing the range until you do get 1 "Jimbo" wrote: Greetings, Im looking for an explanation for something happening in Excel that I dont understand. In a worksheet cell I have a formula =SUMPRODUCT(--([Data.xls]A!$H$2:$H$5000=Asia),--([Data.xls]A!$AN$2:$AN$5000)) which works fine. Regardless of how I try to do it, I cant copy it to any cell on any other worksheet without an error #VALUE! rising from the --([Data.xls]A!$AN$2:$AN$5000)) array. I dont understand why I get an error when I know the formula works fine. Appreciate whatever help you can give me! Jim |
| All times are GMT +1. The time now is 09:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com