Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT #N/A error
The following formula is returning the #N/A error:
=SUMPRODUCT(('Retail 2006'!$AE$2:$AE$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) The following formula in other worksheets in the same file is working: =SUMPRODUCT(('Retail 2006'!$E$2:$E$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) The only difference is the first argument, which is referring to a column containing store types (Department Stores, Chains etc.) in the worksheet that isn't working and store codes (C10, C20, C30 etc.) in the one that is. Cell formats are set to general for both columns. For your information: Column C contains months of the year Column F contains sales Cells D2 & N2 are linked to combo boxes and display the Store Type and Month respectively. Again, cell formats are General. What could be going wrong? Regards -- R Ormerod |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT #N/A error
on your retail 2006 sheet select all
data-filter-auto filter in column AE filter select custom type in the value for D$2 in column C filter select custom type in the value for N2 check column F for a #NA "R Ormerod" wrote: The following formula is returning the #N/A error: =SUMPRODUCT(('Retail 2006'!$AE$2:$AE$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) The following formula in other worksheets in the same file is working: =SUMPRODUCT(('Retail 2006'!$E$2:$E$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) The only difference is the first argument, which is referring to a column containing store types (Department Stores, Chains etc.) in the worksheet that isn't working and store codes (C10, C20, C30 etc.) in the one that is. Cell formats are set to general for both columns. For your information: Column C contains months of the year Column F contains sales Cells D2 & N2 are linked to combo boxes and display the Store Type and Month respectively. Again, cell formats are General. What could be going wrong? Regards -- R Ormerod |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT #N/A error
This is not practicable because:
a) The data worksheet is an import from a text file which is regularly refreshed b) The formula referring to column C specifies less than or equal to the value in N2 (for YTD sales) Something else - column AE is the last column of data in Retai 2006 and is VLOOKUP formulae (the rest of the data is values). There are #N/A values in this column. -- R Ormerod "bj" wrote: on your retail 2006 sheet select all data-filter-auto filter in column AE filter select custom type in the value for D$2 in column C filter select custom type in the value for N2 check column F for a #NA "R Ormerod" wrote: The following formula is returning the #N/A error: =SUMPRODUCT(('Retail 2006'!$AE$2:$AE$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) The following formula in other worksheets in the same file is working: =SUMPRODUCT(('Retail 2006'!$E$2:$E$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) The only difference is the first argument, which is referring to a column containing store types (Department Stores, Chains etc.) in the worksheet that isn't working and store codes (C10, C20, C30 etc.) in the one that is. Cell formats are set to general for both columns. For your information: Column C contains months of the year Column F contains sales Cells D2 & N2 are linked to combo boxes and display the Store Type and Month respectively. Again, cell formats are General. What could be going wrong? Regards -- R Ormerod |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT #N/A error
Something else - column AE is the last column of data in Retai 2006 and is
VLOOKUP formulae (the rest of the data is values). There are #N/A values in this column. That's why you're getting a #N/A error. You're better off fixing those errors at the source rather than trying to deal with them later (as you've found out). Change your lookup formulas to return blanks or 0's (or some other value) rather then let the formula return an error. =IF(ISNA(VLOOKUP(...........)),"",VLOOKUP(........ ...)) However, if you *want* the #N/A's to be present for whatever reason, try something like this: Column A are your store types Column B are your months Column C are the sales =SUMPRODUCT(--(--(ISNUMBER(--(A1:A5=D2)))=1),--(B1:B5<=N2),C1:C5) Biff "R Ormerod" wrote in message ... The following formula is returning the #N/A error: =SUMPRODUCT(('Retail 2006'!$AE$2:$AE$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) The following formula in other worksheets in the same file is working: =SUMPRODUCT(('Retail 2006'!$E$2:$E$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) The only difference is the first argument, which is referring to a column containing store types (Department Stores, Chains etc.) in the worksheet that isn't working and store codes (C10, C20, C30 etc.) in the one that is. Cell formats are set to general for both columns. For your information: Column C contains months of the year Column F contains sales Cells D2 & N2 are linked to combo boxes and display the Store Type and Month respectively. Again, cell formats are General. What could be going wrong? Regards -- R Ormerod |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT #N/A error
I tried to respond yesterday, but got one of those "No such site messages"?
try =SUMPRODUCT(--not(iserror('Retail 2006'!$AE$2:$AE$4000=$D$2),('Retail 2006'!$AE$2:$AE$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) "R Ormerod" wrote: This is not practicable because: a) The data worksheet is an import from a text file which is regularly refreshed b) The formula referring to column C specifies less than or equal to the value in N2 (for YTD sales) Something else - column AE is the last column of data in Retai 2006 and is VLOOKUP formulae (the rest of the data is values). There are #N/A values in this column. -- R Ormerod "bj" wrote: on your retail 2006 sheet select all data-filter-auto filter in column AE filter select custom type in the value for D$2 in column C filter select custom type in the value for N2 check column F for a #NA "R Ormerod" wrote: The following formula is returning the #N/A error: =SUMPRODUCT(('Retail 2006'!$AE$2:$AE$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) The following formula in other worksheets in the same file is working: =SUMPRODUCT(('Retail 2006'!$E$2:$E$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) The only difference is the first argument, which is referring to a column containing store types (Department Stores, Chains etc.) in the worksheet that isn't working and store codes (C10, C20, C30 etc.) in the one that is. Cell formats are set to general for both columns. For your information: Column C contains months of the year Column F contains sales Cells D2 & N2 are linked to combo boxes and display the Store Type and Month respectively. Again, cell formats are General. What could be going wrong? Regards -- R Ormerod |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT #N/A error
=SUMPRODUCT(--not(iserror('Retail 2006'!$AE$2:$AE$4000=$D$2),('Retail
2006'!$AE$2:$AE$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) Did you test that formula? Aside from the syntax error it still won't work: ('Retail 2006'!$AE$2:$AE$4000=$D$2) That array will still fail on any errors. Biff "bj" wrote in message ... I tried to respond yesterday, but got one of those "No such site messages"? try =SUMPRODUCT(--not(iserror('Retail 2006'!$AE$2:$AE$4000=$D$2),('Retail 2006'!$AE$2:$AE$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) "R Ormerod" wrote: This is not practicable because: a) The data worksheet is an import from a text file which is regularly refreshed b) The formula referring to column C specifies less than or equal to the value in N2 (for YTD sales) Something else - column AE is the last column of data in Retai 2006 and is VLOOKUP formulae (the rest of the data is values). There are #N/A values in this column. -- R Ormerod "bj" wrote: on your retail 2006 sheet select all data-filter-auto filter in column AE filter select custom type in the value for D$2 in column C filter select custom type in the value for N2 check column F for a #NA "R Ormerod" wrote: The following formula is returning the #N/A error: =SUMPRODUCT(('Retail 2006'!$AE$2:$AE$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) The following formula in other worksheets in the same file is working: =SUMPRODUCT(('Retail 2006'!$E$2:$E$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) The only difference is the first argument, which is referring to a column containing store types (Department Stores, Chains etc.) in the worksheet that isn't working and store codes (C10, C20, C30 etc.) in the one that is. Cell formats are set to general for both columns. For your information: Column C contains months of the year Column F contains sales Cells D2 & N2 are linked to combo boxes and display the Store Type and Month respectively. Again, cell formats are General. What could be going wrong? Regards -- R Ormerod |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT #N/A error
Mia culpa; thanks for checking it.
I tested a simplified equation, that when I look at it now was very wrong. I like your method but got stuborn and tried a different version new try again beacause of AE having #N/As =sumproduct(if(iserror('Retail 2006'!$AE$2:$AE$4000=$D$2),0,if('Retail 2006'!$AE$2:$AE$4000=$D$2),1,0),--('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) etered as an array control-shift-enter unless I have made another typo. "T. Valko" wrote: =SUMPRODUCT(--not(iserror('Retail 2006'!$AE$2:$AE$4000=$D$2),('Retail 2006'!$AE$2:$AE$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) Did you test that formula? Aside from the syntax error it still won't work: ('Retail 2006'!$AE$2:$AE$4000=$D$2) That array will still fail on any errors. Biff "bj" wrote in message ... I tried to respond yesterday, but got one of those "No such site messages"? try =SUMPRODUCT(--not(iserror('Retail 2006'!$AE$2:$AE$4000=$D$2),('Retail 2006'!$AE$2:$AE$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) "R Ormerod" wrote: This is not practicable because: a) The data worksheet is an import from a text file which is regularly refreshed b) The formula referring to column C specifies less than or equal to the value in N2 (for YTD sales) Something else - column AE is the last column of data in Retai 2006 and is VLOOKUP formulae (the rest of the data is values). There are #N/A values in this column. -- R Ormerod "bj" wrote: on your retail 2006 sheet select all data-filter-auto filter in column AE filter select custom type in the value for D$2 in column C filter select custom type in the value for N2 check column F for a #NA "R Ormerod" wrote: The following formula is returning the #N/A error: =SUMPRODUCT(('Retail 2006'!$AE$2:$AE$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) The following formula in other worksheets in the same file is working: =SUMPRODUCT(('Retail 2006'!$E$2:$E$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) The only difference is the first argument, which is referring to a column containing store types (Department Stores, Chains etc.) in the worksheet that isn't working and store codes (C10, C20, C30 etc.) in the one that is. Cell formats are set to general for both columns. For your information: Column C contains months of the year Column F contains sales Cells D2 & N2 are linked to combo boxes and display the Store Type and Month respectively. Again, cell formats are General. What could be going wrong? Regards -- R Ormerod |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT #N/A error
That technique will work. But, by using IF this makes the formula an array
formula so your not getting any advantage in using SUMPRODUCT. =SUM(IF(ISTEXT(A1:A5),IF(A1:A5=D2,IF(B1:B5<=N2,C1: C5)))) Biff "bj" wrote in message ... Mia culpa; thanks for checking it. I tested a simplified equation, that when I look at it now was very wrong. I like your method but got stuborn and tried a different version new try again beacause of AE having #N/As =sumproduct(if(iserror('Retail 2006'!$AE$2:$AE$4000=$D$2),0,if('Retail 2006'!$AE$2:$AE$4000=$D$2),1,0),--('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) etered as an array control-shift-enter unless I have made another typo. "T. Valko" wrote: =SUMPRODUCT(--not(iserror('Retail 2006'!$AE$2:$AE$4000=$D$2),('Retail 2006'!$AE$2:$AE$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) Did you test that formula? Aside from the syntax error it still won't work: ('Retail 2006'!$AE$2:$AE$4000=$D$2) That array will still fail on any errors. Biff "bj" wrote in message ... I tried to respond yesterday, but got one of those "No such site messages"? try =SUMPRODUCT(--not(iserror('Retail 2006'!$AE$2:$AE$4000=$D$2),('Retail 2006'!$AE$2:$AE$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) "R Ormerod" wrote: This is not practicable because: a) The data worksheet is an import from a text file which is regularly refreshed b) The formula referring to column C specifies less than or equal to the value in N2 (for YTD sales) Something else - column AE is the last column of data in Retai 2006 and is VLOOKUP formulae (the rest of the data is values). There are #N/A values in this column. -- R Ormerod "bj" wrote: on your retail 2006 sheet select all data-filter-auto filter in column AE filter select custom type in the value for D$2 in column C filter select custom type in the value for N2 check column F for a #NA "R Ormerod" wrote: The following formula is returning the #N/A error: =SUMPRODUCT(('Retail 2006'!$AE$2:$AE$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) The following formula in other worksheets in the same file is working: =SUMPRODUCT(('Retail 2006'!$E$2:$E$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000) The only difference is the first argument, which is referring to a column containing store types (Department Stores, Chains etc.) in the worksheet that isn't working and store codes (C10, C20, C30 etc.) in the one that is. Cell formats are set to general for both columns. For your information: Column C contains months of the year Column F contains sales Cells D2 & N2 are linked to combo boxes and display the Store Type and Month respectively. Again, cell formats are General. What could be going wrong? Regards -- R Ormerod |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct value error | Excel Worksheet Functions | |||
Sumproduct error | Excel Worksheet Functions | |||
#ref error with Sumproduct | Excel Worksheet Functions | |||
Sumproduct error | Excel Worksheet Functions | |||
SUMPRODUCT ERROR | Excel Discussion (Misc queries) |