| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
N/A
Hello from Steved
Please if a cell has in it "N/A" What is require in the bootom formula to reconize thia please as at it is at the moment it returns value in the sum column, meaning I have to change it from N/A to 0 for the formula to work thanks. =SUMPRODUCT(--('From Charters'!$A$9=$A$41)*'From Charters'!$D$9:$E$9) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
N/A
If you actually mean #N/A, then it is ISNA or ISERROR, or maybe conversely
test for ISNUMBER for validity. You formula will not work =SUMPRODUCT(--('From Charters'!$A$9=$A$41)*'From Charters'!$D$9:$E$9) as the ranges must be the same size, perhaps you really mean =SUMPRODUCT(--('From Charters'!$A$9:$B$9=$A$41)*'From Charters'!$D$9:$E$9) and you don't need the first -- Test that with =SUMPRODUCT(('From Charters'!$A$9:$B$9=$A$41))*(ISNUMBER('From Charters'!$D$9:$E$9)) But this is all guesswork. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Steved" wrote in message ... Hello from Steved Please if a cell has in it "N/A" What is require in the bootom formula to reconize thia please as at it is at the moment it returns value in the sum column, meaning I have to change it from N/A to 0 for the formula to work thanks. =SUMPRODUCT(--('From Charters'!$A$9=$A$41)*'From Charters'!$D$9:$E$9) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
N/A
Try this:
=('From Charters'!$A$9=$A$41)*SUM('From Charters'!$D$9:$E$9) Biff "Steved" wrote in message ... Hello from Steved Please if a cell has in it "N/A" What is require in the bootom formula to reconize thia please as at it is at the moment it returns value in the sum column, meaning I have to change it from N/A to 0 for the formula to work thanks. =SUMPRODUCT(--('From Charters'!$A$9=$A$41)*'From Charters'!$D$9:$E$9) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
N/A
Thankyou both It works as I intended.
Cheers "Biff" wrote: Try this: =('From Charters'!$A$9=$A$41)*SUM('From Charters'!$D$9:$E$9) Biff "Steved" wrote in message ... Hello from Steved Please if a cell has in it "N/A" What is require in the bootom formula to reconize thia please as at it is at the moment it returns value in the sum column, meaning I have to change it from N/A to 0 for the formula to work thanks. =SUMPRODUCT(--('From Charters'!$A$9=$A$41)*'From Charters'!$D$9:$E$9) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
