Excel gurus - using sum on column with some non numerics

198 Views 1 Reply 2 Participants Last post by ripTOOL, Dec 31, 2003

JohnnyRad

Discussion Starter · Dec 31, 2003

I've got a column that is all Vlookups. On some of the lines, there are #N/A's because it couldn't find the look up... i want these to show as they are. I also want a total at the bottom though of the values that were found, and just using SUM doesn't work because of the NA's. I could manually do A+B+C etc but its a decent sized list.... I looked into the SUMIF formula and im not sure if thatll work... if theres a way to tell it sumif its a numerical value or sumif the cell doesnt = #N/A ? Or maybe there's some other way to do it? I haven't really used sumif before.

Thanks in advance... itll save me a headache and a half.

Save

1 - 2 of 2 Posts

ripTOOL

· #2 · Dec 31, 2003 (Edited)

If there is no option to tell it what to put in the vlookup when it fails then how a bout this...

make an extra blank column. Make an if statement that fills the column with the same values as the old column.
Like :
If a1 = #n/a then 0 else a1.
Think it'd go like this, if(a1="#n/a",0,a1)

for the #n/a you just type in the text '#n/a'
So when it sees the #n/a it puts a zero and when it sees the number it just copies the number to the new column. Then add up that column or hide it and add it up under the correct column.

:hail

EDIT:
Also, if you format the column for numbers only, does it put the #n/a when it comes up with a bad answer or does it put 'zero'? I cant remember off hand.

See less See more

Save

1 - 2 of 2 Posts

This is an older thread, you may not receive a response, and could be reviving an old thread. Please consider creating a new thread.

Top Contributors this Month