| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT formula help
The formula below was working when D188 was D14 because I had entries from D9
to D14, but when I copied down to row 188 where there is no data yet, it created a "#N/A" for result. I need to have those cell without data to be included in the formula. Can someone help with this problem? Many thanks in advance. Serge =IF(OR(A32="",B32=""),"",SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188="SAE Gr.5 Min. Cad. Plated"),'Bolt List'!$C$9:$C$188)) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT formula help
why not change your formula so there is no na by using the same if idea
shown. -- Don Guillett SalesAid Software "Serge" wrote in message ... The formula below was working when D188 was D14 because I had entries from D9 to D14, but when I copied down to row 188 where there is no data yet, it created a "#N/A" for result. I need to have those cell without data to be included in the formula. Can someone help with this problem? Many thanks in advance. Serge =IF(OR(A32="",B32=""),"",SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188="SAE Gr.5 Min. Cad. Plated"),'Bolt List'!$C$9:$C$188)) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT formula help
Hello Don,
Could you show me an example please! Serge "Don Guillett" wrote: why not change your formula so there is no na by using the same if idea shown. -- Don Guillett SalesAid Software "Serge" wrote in message ... The formula below was working when D188 was D14 because I had entries from D9 to D14, but when I copied down to row 188 where there is no data yet, it created a "#N/A" for result. I need to have those cell without data to be included in the formula. Can someone help with this problem? Many thanks in advance. Serge =IF(OR(A32="",B32=""),"",SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188="SAE Gr.5 Min. Cad. Plated"),'Bolt List'!$C$9:$C$188)) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT formula help
The formula is fine. You must have a #N/A in the data between 15 and 188.
Find it and correct it. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Serge" wrote in message ... The formula below was working when D188 was D14 because I had entries from D9 to D14, but when I copied down to row 188 where there is no data yet, it created a "#N/A" for result. I need to have those cell without data to be included in the formula. Can someone help with this problem? Many thanks in advance. Serge =IF(OR(A32="",B32=""),"",SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188="SAE Gr.5 Min. Cad. Plated"),'Bolt List'!$C$9:$C$188)) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT formula help
Hello Bob,
Yes I have in the "Bolt List" sheet for all the rows that do not have entries yet with this formula: =VLOOKUP(I40, INDIRECT(VLOOKUP(D40, $R$1:$S$9,2)),2,TRUE) Now maybe the VLOOKUP formula needs modification? Can the this formula give me an empty cell if there is no entries in the corresponding cells? Thanks a million Serge "Bob Phillips" wrote: The formula is fine. You must have a #N/A in the data between 15 and 188. Find it and correct it. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Serge" wrote in message ... The formula below was working when D188 was D14 because I had entries from D9 to D14, but when I copied down to row 188 where there is no data yet, it created a "#N/A" for result. I need to have those cell without data to be included in the formula. Can someone help with this problem? Many thanks in advance. Serge =IF(OR(A32="",B32=""),"",SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188="SAE Gr.5 Min. Cad. Plated"),'Bolt List'!$C$9:$C$188)) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT formula help
Yes, use
=IF(ISNA(VLOOKUP(I40, INDIRECT(VLOOKUP(D40, $R$1:$S$9,2)),2,TRUE)),"",VLOOKUP(I40, INDIRECT(VLOOKUP(D40, $R$1:$S$9,2)),2,TRUE) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Serge" wrote in message ... Hello Bob, Yes I have in the "Bolt List" sheet for all the rows that do not have entries yet with this formula: =VLOOKUP(I40, INDIRECT(VLOOKUP(D40, $R$1:$S$9,2)),2,TRUE) Now maybe the VLOOKUP formula needs modification? Can the this formula give me an empty cell if there is no entries in the corresponding cells? Thanks a million Serge "Bob Phillips" wrote: The formula is fine. You must have a #N/A in the data between 15 and 188. Find it and correct it. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Serge" wrote in message ... The formula below was working when D188 was D14 because I had entries from D9 to D14, but when I copied down to row 188 where there is no data yet, it created a "#N/A" for result. I need to have those cell without data to be included in the formula. Can someone help with this problem? Many thanks in advance. Serge =IF(OR(A32="",B32=""),"",SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188="SAE Gr.5 Min. Cad. Plated"),'Bolt List'!$C$9:$C$188)) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT formula help
I have learned a lot.
Thank so much Bob and to everyone else that had an input in my request. "Bob Phillips" wrote: Yes, use =IF(ISNA(VLOOKUP(I40, INDIRECT(VLOOKUP(D40, $R$1:$S$9,2)),2,TRUE)),"",VLOOKUP(I40, INDIRECT(VLOOKUP(D40, $R$1:$S$9,2)),2,TRUE) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Serge" wrote in message ... Hello Bob, Yes I have in the "Bolt List" sheet for all the rows that do not have entries yet with this formula: =VLOOKUP(I40, INDIRECT(VLOOKUP(D40, $R$1:$S$9,2)),2,TRUE) Now maybe the VLOOKUP formula needs modification? Can the this formula give me an empty cell if there is no entries in the corresponding cells? Thanks a million Serge "Bob Phillips" wrote: The formula is fine. You must have a #N/A in the data between 15 and 188. Find it and correct it. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Serge" wrote in message ... The formula below was working when D188 was D14 because I had entries from D9 to D14, but when I copied down to row 188 where there is no data yet, it created a "#N/A" for result. I need to have those cell without data to be included in the formula. Can someone help with this problem? Many thanks in advance. Serge =IF(OR(A32="",B32=""),"",SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188="SAE Gr.5 Min. Cad. Plated"),'Bolt List'!$C$9:$C$188)) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT formula help
Glad we could help Serge.
Regards Bob "Serge" wrote in message ... I have learned a lot. Thank so much Bob and to everyone else that had an input in my request. "Bob Phillips" wrote: Yes, use =IF(ISNA(VLOOKUP(I40, INDIRECT(VLOOKUP(D40, $R$1:$S$9,2)),2,TRUE)),"",VLOOKUP(I40, INDIRECT(VLOOKUP(D40, $R$1:$S$9,2)),2,TRUE) -- HTH Bob Phillips (remove nothere from email address if mailing direct) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| do I need array formula or sumproduct for counting? | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Modify A SumProduct Formula | Excel Worksheet Functions | |||
| sumproduct formula to slow | Excel Worksheet Functions | |||
| adding two sumproduct formulas together | Excel Worksheet Functions | |||
Linear Mode
