Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to SUM #N/A
I have VLOOKUPs that return the value #N/A on occasion, as a matter or
course. I SUM those values. Currently I copy/paste values on the results, then sort out and delete the #N/As to make the SUM work. Is there a better way? Can you alter the SUM sytax to force it to skip over #N/As? Or can you imbed an IF/THEN statement arount the VLOOKUP to display #N/As as zero? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to SUM #N/A
How about this array Paste formula into cell then press Ctrl + shift + enter =SUM(IF(ISERROR(AA5:AA350),"",AA5:AA350)) -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=553277 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to SUM #N/A
hi,
you can imbed in the if by using the iserror function, so you would have =IF(ISERROR(A1),0,A1) for example. let me know if have any problems Looking-for-a-brainiac wrote: I have VLOOKUPs that return the value #N/A on occasion, as a matter or course. I SUM those values. Currently I copy/paste values on the results, then sort out and delete the #N/As to make the SUM work. Is there a better way? Can you alter the SUM sytax to force it to skip over #N/As? Or can you imbed an IF/THEN statement arount the VLOOKUP to display #N/As as zero? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to SUM #N/A
Got it -- it works. THANKS fellas!
"kempo" wrote: hi, you can imbed in the if by using the iserror function, so you would have =IF(ISERROR(A1),0,A1) for example. let me know if have any problems Looking-for-a-brainiac wrote: I have VLOOKUPs that return the value #N/A on occasion, as a matter or course. I SUM those values. Currently I copy/paste values on the results, then sort out and delete the #N/As to make the SUM work. Is there a better way? Can you alter the SUM sytax to force it to skip over #N/As? Or can you imbed an IF/THEN statement arount the VLOOKUP to display #N/As as zero? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to SUM #N/A
=SUM(SUMIF(Range,{"<0","0"}))
Looking-for-a-brainiac wrote: I have VLOOKUPs that return the value #N/A on occasion, as a matter or course. I SUM those values. Currently I copy/paste values on the results, then sort out and delete the #N/As to make the SUM work. Is there a better way? Can you alter the SUM sytax to force it to skip over #N/As? Or can you imbed an IF/THEN statement arount the VLOOKUP to display #N/As as zero? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to SUM #N/A
Or - the second option you mentioned (IF..)
=IF(ISNA(VLOOKUP(A1,range,col,index)),"0",VLOOKUP( A1,range,col,index)) Which will return a zero instead of an #N/A. The statement checks for an N/A error and returns a 0. Help search on ISNA for more details. HTH -Jay- On 26/6/06 07:00, in article , "Aladin Akyurek" wrote: =SUM(SUMIF(Range,{"<0","0"})) Looking-for-a-brainiac wrote: I have VLOOKUPs that return the value #N/A on occasion, as a matter or course. I SUM those values. Currently I copy/paste values on the results, then sort out and delete the #N/As to make the SUM work. Is there a better way? Can you alter the SUM sytax to force it to skip over #N/As? Or can you imbed an IF/THEN statement arount the VLOOKUP to display #N/As as zero? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to SUM #N/A
Nel post
*Jay* ha scritto: Or - the second option you mentioned (IF..) =IF(ISNA(VLOOKUP(A1,range,col,index)),"0",VLOOKUP( A1,range,col,index)) Which will return a zero instead of an #N/A. The statement checks for an N/A error and returns a 0. Help search on ISNA for more details. HTH -Jay- No Jay, your formula will not return a zero instead of an #N/A, but a string which has just one character 0, so should be better write 0 without quotes: quotes are needed just for text, not for numbers. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to SUM #N/A
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to SUM #N/A
On 27/6/06 15:17, in article , "Franz
Verga" wrote: Nel post *Jay* ha scritto: On 27/6/06 10:17, in article , "Franz Verga" wrote: Nel post *Jay* ha scritto: Or - the second option you mentioned (IF..) =IF(ISNA(VLOOKUP(A1,range,col,index)),"0",VLOOKUP( A1,range,col,index)) Which will return a zero instead of an #N/A. The statement checks for an N/A error and returns a 0. Help search on ISNA for more details. HTH -Jay- No Jay, your formula will not return a zero instead of an #N/A, but a string which has just one character 0, so should be better write 0 without quotes: quotes are needed just for text, not for numbers. Thanks Franz, I didn't realise I'd done that - (wrote it without thinking). Am I correct in thinking the 0 string could still have been recognised as a number though, for the SUM? -Jay- No, it's note recognized, just skipped like blanks or null strings, also words: you can try this; input: A1 == 56, A2 == ="", A3 == ="0", A4 == mouse, A5 == 21 A6 == =SUM(A1:A5) The result in A6 is 77... Well it would be *even* if the 0 string was recognised as a number, with it being zero, but I know what you mean - 'Number' Text strings aren't recognised as numbersand skipped like nulls. However, can you explain this: A4 == 7 A5 == "2" SUM(A4:A5) gives 7 (As expected,the "2" string isn't recognised as a num) =A4*A5 gives 14 =SUM(A4*A5) gives 14 Why is the "2" text string not recognised as a number by the function, but *is* when used in a direct mathematical operation, A4*A5 ? -Jay- |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to SUM #N/A
Nel post
*Jay* ha scritto: On 27/6/06 15:17, in article , "Franz Verga" wrote: Nel post *Jay* ha scritto: On 27/6/06 10:17, in article , "Franz Verga" wrote: Nel post *Jay* ha scritto: Or - the second option you mentioned (IF..) =IF(ISNA(VLOOKUP(A1,range,col,index)),"0",VLOOKUP( A1,range,col,index)) Which will return a zero instead of an #N/A. The statement checks for an N/A error and returns a 0. Help search on ISNA for more details. HTH -Jay- No Jay, your formula will not return a zero instead of an #N/A, but a string which has just one character 0, so should be better write 0 without quotes: quotes are needed just for text, not for numbers. Thanks Franz, I didn't realise I'd done that - (wrote it without thinking). Am I correct in thinking the 0 string could still have been recognised as a number though, for the SUM? -Jay- No, it's note recognized, just skipped like blanks or null strings, also words: you can try this; input: A1 == 56, A2 == ="", A3 == ="0", A4 == mouse, A5 == 21 A6 == =SUM(A1:A5) The result in A6 is 77... Well it would be *even* if the 0 string was recognised as a number, with it being zero, but I know what you mean - 'Number' Text strings aren't recognised as numbersand skipped like nulls. However, can you explain this: A4 == 7 A5 == "2" SUM(A4:A5) gives 7 (As expected,the "2" string isn't recognised as a num) =A4*A5 gives 14 =SUM(A4*A5) gives 14 Why is the "2" text string not recognised as a number by the function, but *is* when used in a direct mathematical operation, A4*A5 ? -Jay- Because Excel do an implicit conversion, when you use strings as arguments in calculation Excel try to "translate" the strings in numbers, but the function don't try to do this translation, because they don't have the values , just the references, so they use the range as they are. But if you put a string inside the SUM function you will be surprised, because Excel now do the translation, so, for example =SUM("22",5) returns 27. Also if you input A1 == =5<6, A2 == 6 and the try to sum, if you do: =SUM(A1:A2) the results would be 6, but if you do =A1+A2 you will have 7 and also if you do SUM(5<6,6) you will have 7. This is because generally speaking functions inputs are references, not values, but if you input values in a function, the function would try to translate that value in a number. -- Thanks in advance for your feedback. Ciao Franz Verga from Italy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|