Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help on sumproduct returning errors
Biff or anybody. . .
The formula works well, however When using the answer below, I get a #VALUE! OR #DIV/0! if a looked up value from the B10:S10 range (labels 1 to 18) isn't found in its spot in the corresponding verticle column. Prefacing the formula with =IF(ISERROR(formula),"",etc or =IF(ISNA),"",etc does not return a blank cell. How might I return a blank if the value isn't found? Thanks for any assistance. Pierre from Hi! Try this: This data in the range A2:C5 12587-A 1 2 12587-A 3 2.8 12587-A 4 8 12587-A 6 65 A10 = 12587-A B10:S10 = 1,2,3,4,5....18 Formula in B11 copied across: =IF(SUMPRODUCT(--($A$2:$A$5=$A10),--($B$2:$B$5=B10),$C$2:$C$5)=0,"",SUMPROD*UCT(--($A$2:$A$5=$A10),--($B$2:$B$5=B10),$C$2:$C$5)) This assumes that there will be no duplicates. Such as: 12587-A 1 2 12587-A 1 2.8 12587-A 1 8 12587-A 6 65 Biff |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help on sumproduct returning errors
Works fine for me, I get blank when not found.
Biff checks the value for 0 and puts "" in if so. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Pierre" wrote in message oups.com... Biff or anybody. . . The formula works well, however When using the answer below, I get a #VALUE! OR #DIV/0! if a looked up value from the B10:S10 range (labels 1 to 18) isn't found in its spot in the corresponding verticle column. Prefacing the formula with =IF(ISERROR(formula),"",etc or =IF(ISNA),"",etc does not return a blank cell. How might I return a blank if the value isn't found? Thanks for any assistance. Pierre from Hi! Try this: This data in the range A2:C5 12587-A 1 2 12587-A 3 2.8 12587-A 4 8 12587-A 6 65 A10 = 12587-A B10:S10 = 1,2,3,4,5....18 Formula in B11 copied across: =IF(SUMPRODUCT(--($A$2:$A$5=$A10),--($B$2:$B$5=B10),$C$2:$C$5)=0,"",SUMPROD* UCT(--($A$2:$A$5=$A10),--($B$2:$B$5=B10),$C$2:$C$5)) This assumes that there will be no duplicates. Such as: 12587-A 1 2 12587-A 1 2.8 12587-A 1 8 12587-A 6 65 Biff |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help on sumproduct returning errors
Bob Phillips wrote: Works fine for me, I get blank when not found. Biff checks the value for 0 and puts "" in if so. -- HTH Bob Phillips Bob, checked my cell references. Works fine now. Thanks for the validation. Pierre |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT returning incorrect result | Excel Worksheet Functions | |||
VLOOKUP returning errors | Excel Worksheet Functions | |||
SUMPRODUCT formula returning #VALUE! error | Excel Worksheet Functions | |||
sumproduct returning #NA | Excel Discussion (Misc queries) | |||
sumproduct causing memory errors? | Excel Worksheet Functions |