Home |
Search |
Today's Posts |
#1
|
|||
|
|||
getting the sum of a column with #N/A values in it
I created a VLookup formula to display the value of an item if it exists. If
the value doesn't exist, it displays an #N/A. 1) Can I modify this formula to display a "0" instead of an #N/A? and 2) How can I total a column which only excludes #N/A? A copy of my formula is below. =VLOOKUP(B156,'2003'!B:T,3,0) Thank you very much. Moy Emrick |
#2
|
|||
|
|||
getting the sum of a column with #N/A values in it
=if(iserror(yourvlookupformula),0,yourvlookupformu la)
And if you show 0's, you won't need to worry about that sum formula. But if you want... =sum(if(isnumber(a1:a10),a1:a10)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. Or if all your errors are #n/a's, you could use: =SUMIF(A1:A10,"<#n/a") (not an array formula) Moy Emrick wrote: I created a VLookup formula to display the value of an item if it exists. If the value doesn't exist, it displays an #N/A. 1) Can I modify this formula to display a "0" instead of an #N/A? and 2) How can I total a column which only excludes #N/A? A copy of my formula is below. =VLOOKUP(B156,'2003'!B:T,3,0) Thank you very much. Moy Emrick -- Dave Peterson |
#3
|
|||
|
|||
getting the sum of a column with #N/A values in it
Hi!
1. =IF(COUNTIF(2003'!B:B,B156),VLOOKUP(B156,2003'!B:T ,3,0),0) 2. =SUMIF(A1:A100,"<#N/A") Biff "Moy Emrick" <Moy wrote in message ... I created a VLookup formula to display the value of an item if it exists. If the value doesn't exist, it displays an #N/A. 1) Can I modify this formula to display a "0" instead of an #N/A? and 2) How can I total a column which only excludes #N/A? A copy of my formula is below. =VLOOKUP(B156,'2003'!B:T,3,0) Thank you very much. Moy Emrick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pbl calculating US$ values | Excel Discussion (Misc queries) | |||
match and count words | Excel Worksheet Functions | |||
Lookup values in a column and display them in order with no gaps | Excel Worksheet Functions | |||
Move column values w/o formula | Excel Worksheet Functions | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions |