| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error problem
I have this formular:
=SUMPRODUCT(--(Tables!$C$2:$C$2979=$A4),--(Tables!$D$2:$D$2979=$B4),--(Tables!$E$2:$E$2979="Yes")) but it is showing as an error. Iknow there is no errors in there but when I click the Trace Error option I get a little "Table" icon and I don't know what that is or what it means. Any ideas? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Error problem
Hi,
There's nothing wrong with the formula so it must be the data, what 'error' are you getting? Mike "Ayo" wrote: I have this formular: =SUMPRODUCT(--(Tables!$C$2:$C$2979=$A4),--(Tables!$D$2:$D$2979=$B4),--(Tables!$E$2:$E$2979="Yes")) but it is showing as an error. Iknow there is no errors in there but when I click the Trace Error option I get a little "Table" icon and I don't know what that is or what it means. Any ideas? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Error problem
Hi,
the missing part in the formula is where you want to pull the information if the three conditions are met, let's say column F =SUMPRODUCT(--(Tables!$C$2:$C$2979=$A4),--(Tables!$D$2:$D$2979=$B4),--(Tables!$E$2:$E$2979="Yes"),Tables!$F$2:$F$2979) "Ayo" wrote: I have this formular: =SUMPRODUCT(--(Tables!$C$2:$C$2979=$A4),--(Tables!$D$2:$D$2979=$B4),--(Tables!$E$2:$E$2979="Yes")) but it is showing as an error. Iknow there is no errors in there but when I click the Trace Error option I get a little "Table" icon and I don't know what that is or what it means. Any ideas? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
N/A error
I get the #N/A
|
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Error problem
Look for error(s) in one (or all) of those ranges.
Remember to look in hidden rows, too. Ayo wrote: I have this formular: =SUMPRODUCT(--(Tables!$C$2:$C$2979=$A4),--(Tables!$D$2:$D$2979=$B4),--(Tables!$E$2:$E$2979="Yes")) but it is showing as an error. Iknow there is no errors in there but when I click the Trace Error option I get a little "Table" icon and I don't know what that is or what it means. Any ideas? -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Error problem
No hidden rows and no links to other tables, which was my first suspision
"Dave Peterson" wrote: Look for error(s) in one (or all) of those ranges. Remember to look in hidden rows, too. Ayo wrote: I have this formular: =SUMPRODUCT(--(Tables!$C$2:$C$2979=$A4),--(Tables!$D$2:$D$2979=$B4),--(Tables!$E$2:$E$2979="Yes")) but it is showing as an error. Iknow there is no errors in there but when I click the Trace Error option I get a little "Table" icon and I don't know what that is or what it means. Any ideas? -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Error problem
Did you look for errors in any of those ranges?
Ayo wrote: No hidden rows and no links to other tables, which was my first suspision "Dave Peterson" wrote: Look for error(s) in one (or all) of those ranges. Remember to look in hidden rows, too. Ayo wrote: I have this formular: =SUMPRODUCT(--(Tables!$C$2:$C$2979=$A4),--(Tables!$D$2:$D$2979=$B4),--(Tables!$E$2:$E$2979="Yes")) but it is showing as an error. Iknow there is no errors in there but when I click the Trace Error option I get a little "Table" icon and I don't know what that is or what it means. Any ideas? -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Error problem
I have been going over and over all of the data and I can't find anything. I
tried the help function in excel and I learned that when I see "a black arrow points from the selected cell to a worksheet icon" that the cell "is referenced by a cell on another worksheet or workbook". I checked and there are no references. I deleted all the references in the Name Manager also. "Dave Peterson" wrote: Did you look for errors in any of those ranges? Ayo wrote: No hidden rows and no links to other tables, which was my first suspision "Dave Peterson" wrote: Look for error(s) in one (or all) of those ranges. Remember to look in hidden rows, too. Ayo wrote: I have this formular: =SUMPRODUCT(--(Tables!$C$2:$C$2979=$A4),--(Tables!$D$2:$D$2979=$B4),--(Tables!$E$2:$E$2979="Yes")) but it is showing as an error. Iknow there is no errors in there but when I click the Trace Error option I get a little "Table" icon and I don't know what that is or what it means. Any ideas? -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Error problem
Are you sure that the formula you posted is what was used in the cell?
Your formula doesn't really look like: =table(x1,y1) does it? Ayo wrote: I have been going over and over all of the data and I can't find anything. I tried the help function in excel and I learned that when I see "a black arrow points from the selected cell to a worksheet icon" that the cell "is referenced by a cell on another worksheet or workbook". I checked and there are no references. I deleted all the references in the Name Manager also. "Dave Peterson" wrote: Did you look for errors in any of those ranges? Ayo wrote: No hidden rows and no links to other tables, which was my first suspision "Dave Peterson" wrote: Look for error(s) in one (or all) of those ranges. Remember to look in hidden rows, too. Ayo wrote: I have this formular: =SUMPRODUCT(--(Tables!$C$2:$C$2979=$A4),--(Tables!$D$2:$D$2979=$B4),--(Tables!$E$2:$E$2979="Yes")) but it is showing as an error. Iknow there is no errors in there but when I click the Trace Error option I get a little "Table" icon and I don't know what that is or what it means. Any ideas? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Error problem
This is exactly what is in the cell:
=SUMPRODUCT(--(Tables!$C$2:$C$2958=$A4),--(Tables!$D$2:$D$2958=$B4),--(Tables!$E$2:$E$2958="Yes")) "Dave Peterson" wrote: Are you sure that the formula you posted is what was used in the cell? Your formula doesn't really look like: =table(x1,y1) does it? Ayo wrote: I have been going over and over all of the data and I can't find anything. I tried the help function in excel and I learned that when I see "a black arrow points from the selected cell to a worksheet icon" that the cell "is referenced by a cell on another worksheet or workbook". I checked and there are no references. I deleted all the references in the Name Manager also. "Dave Peterson" wrote: Did you look for errors in any of those ranges? Ayo wrote: No hidden rows and no links to other tables, which was my first suspision "Dave Peterson" wrote: Look for error(s) in one (or all) of those ranges. Remember to look in hidden rows, too. Ayo wrote: I have this formular: =SUMPRODUCT(--(Tables!$C$2:$C$2979=$A4),--(Tables!$D$2:$D$2979=$B4),--(Tables!$E$2:$E$2979="Yes")) but it is showing as an error. Iknow there is no errors in there but when I click the Trace Error option I get a little "Table" icon and I don't know what that is or what it means. Any ideas? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Error problem
So, if you are getting #N/A as an answer, you have #N/A in at least one of
the cells referenced in your formula. The table icon means, as help told you, "that the cell "is referenced by a cell on another worksheet or workbook". ". You therefore need to go to the worksheet named "Tables", and on that sheet you need to look in the range C2:E2958, as it is in that range that you have at least one cell containing the N/A error. You can find which row(s) the error is in by putting the formula =ISNA(SUM(C2:E2)) in a spare cell in row 2, and copying down, and similarly you can find out which column(s) by putting =ISNA(SUM(C2:C2958)) in a spare cell in column C, and copying across. -- David Biddulph "Ayo" wrote in message ... This is exactly what is in the cell: =SUMPRODUCT(--(Tables!$C$2:$C$2958=$A4),--(Tables!$D$2:$D$2958=$B4),--(Tables!$E$2:$E$2958="Yes")) "Dave Peterson" wrote: Are you sure that the formula you posted is what was used in the cell? Your formula doesn't really look like: =table(x1,y1) does it? Ayo wrote: I have been going over and over all of the data and I can't find anything. I tried the help function in excel and I learned that when I see "a black arrow points from the selected cell to a worksheet icon" that the cell "is referenced by a cell on another worksheet or workbook". I checked and there are no references. I deleted all the references in the Name Manager also. "Dave Peterson" wrote: Did you look for errors in any of those ranges? Ayo wrote: No hidden rows and no links to other tables, which was my first suspision "Dave Peterson" wrote: Look for error(s) in one (or all) of those ranges. Remember to look in hidden rows, too. Ayo wrote: I have this formular: =SUMPRODUCT(--(Tables!$C$2:$C$2979=$A4),--(Tables!$D$2:$D$2979=$B4),--(Tables!$E$2:$E$2979="Yes")) but it is showing as an error. Iknow there is no errors in there but when I click the Trace Error option I get a little "Table" icon and I don't know what that is or what it means. Any ideas? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Error problem
Thanks David. That was it.
"David Biddulph" wrote: So, if you are getting #N/A as an answer, you have #N/A in at least one of the cells referenced in your formula. The table icon means, as help told you, "that the cell "is referenced by a cell on another worksheet or workbook". ". You therefore need to go to the worksheet named "Tables", and on that sheet you need to look in the range C2:E2958, as it is in that range that you have at least one cell containing the N/A error. You can find which row(s) the error is in by putting the formula =ISNA(SUM(C2:E2)) in a spare cell in row 2, and copying down, and similarly you can find out which column(s) by putting =ISNA(SUM(C2:C2958)) in a spare cell in column C, and copying across. -- David Biddulph "Ayo" wrote in message ... This is exactly what is in the cell: =SUMPRODUCT(--(Tables!$C$2:$C$2958=$A4),--(Tables!$D$2:$D$2958=$B4),--(Tables!$E$2:$E$2958="Yes")) "Dave Peterson" wrote: Are you sure that the formula you posted is what was used in the cell? Your formula doesn't really look like: =table(x1,y1) does it? Ayo wrote: I have been going over and over all of the data and I can't find anything. I tried the help function in excel and I learned that when I see "a black arrow points from the selected cell to a worksheet icon" that the cell "is referenced by a cell on another worksheet or workbook". I checked and there are no references. I deleted all the references in the Name Manager also. "Dave Peterson" wrote: Did you look for errors in any of those ranges? Ayo wrote: No hidden rows and no links to other tables, which was my first suspision "Dave Peterson" wrote: Look for error(s) in one (or all) of those ranges. Remember to look in hidden rows, too. Ayo wrote: I have this formular: =SUMPRODUCT(--(Tables!$C$2:$C$2979=$A4),--(Tables!$D$2:$D$2979=$B4),--(Tables!$E$2:$E$2979="Yes")) but it is showing as an error. Iknow there is no errors in there but when I click the Trace Error option I get a little "Table" icon and I don't know what that is or what it means. Any ideas? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
| I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
| On Error Resume Next problem | Excel Discussion (Misc queries) | |||
| OUch, Error Problem.... | Excel Discussion (Misc queries) | |||
| Problem with #VALUE/0! error | Excel Worksheet Functions | |||
Linear Mode
