vlookup, #N/A issue, and SUM problem

julios

New Member
Joined
Dec 23, 2010
Messages
18
First of all I have to say I am an amateur at excel. Secondly my question regards google spreadsheets, but i think it should be the same functions.

I am trying to make a google spreadsheet with the attendances of basketball games. I am giving a link to the current one. The problem is that I want to make it produce results with as little input data as possible. I am using two sheets, one with the stats and one with the results (data goes into "results" and stats should be produced in "stats" sheets).

I want to put in the results in the second sheet and have stats produced in the first. This is no problem so far as I use vlookup (cell, sheet!A:1:A2, 4, false i.e.) giving the range etc (cells b7 and b8 include the formula as an example)

The problem is that when i use vlookup for example in column B of sheet 1 (stats) I get as expected many #N/As and subsequently in cell B31 (stats sheet) called "TOTAL" I also get a #N/A as it has a SUM function of the column.

Is there any way I can have it ignore the #N/A (and hide them) and produce a SUM result of the column, or do I have to put in the data manually in sheet "stats" also?

thanks in advance

link to the spreadsheet

https://spreadsheets.google.com/spr...GF0Yk55d3Q2Q2RsYy1BR2xVRU5OVWc&hl=en_US#gid=0
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi,

Haven't looked at your spreadsheet but can you create another column and reference the first using something like =IF(ISNA(A1),0,A1)

Cheers, Glenn.
 
Upvote 0
First of all I have to say I am an amateur at excel. Secondly my question regards google spreadsheets, but i think it should be the same functions.

I am trying to make a google spreadsheet with the attendances of basketball games. I am giving a link to the current one. The problem is that I want to make it produce results with as little input data as possible. I am using two sheets, one with the stats and one with the results (data goes into "results" and stats should be produced in "stats" sheets).

I want to put in the results in the second sheet and have stats produced in the first. This is no problem so far as I use vlookup (cell, sheet!A:1:A2, 4, false i.e.) giving the range etc (cells b7 and b8 include the formula as an example)

The problem is that when i use vlookup for example in column B of sheet 1 (stats) I get as expected many #N/As and subsequently in cell B31 (stats sheet) called "TOTAL" I also get a #N/A as it has a SUM function of the column.

Is there any way I can have it ignore the #N/A (and hide them) and produce a SUM result of the column, or do I have to put in the data manually in sheet "stats" also?

thanks in advance

link to the spreadsheet

https://spreadsheets.google.com/spr...GF0Yk55d3Q2Q2RsYy1BR2xVRU5OVWc&hl=en_US#gid=0
The generic method is something like this:

=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

I don't know if that'll work in a Google SS, though.
 
Upvote 0
Hi,

Haven't looked at your spreadsheet but can you create another column and reference the first using something like =IF(ISNA(A1),0,A1)

Cheers, Glenn.

As I said I am an amateur. not quite sure what you mean. I set the spreadsheet so that anyone can edit. Please do'nt destroy it, lol:laugh:

Care to post the formula that produces #N/A?

=vlookup(A8,results!G4:J15,4,false)

The generic method is something like this:

=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

I don't know if that'll work in a Google SS, though.

it is the same but it also washed away the desired results in the column. unless you mean i have to apply it on each cell that produces a #N/A which doesn't do it for me as i don't know in advance which cells are going to produce that

Spreadsheet is open, thanks for the quick responses

https://spreadsheets.google.com/spr...GF0Yk55d3Q2Q2RsYy1BR2xVRU5OVWc&hl=en_US#gid=0
 
Upvote 0
As I said I am an amateur. not quite sure what you mean. I set the spreadsheet so that anyone can edit. Please do'nt destroy it, lol:laugh:



=vlookup(A8,results!G4:J15,4,false)



it is the same but it also washed away the desired results in the column. unless you mean i have to apply it on each cell that produces a #N/A which doesn't do it for me as i don't know in advance which cells are going to produce that

Spreadsheet is open, thanks for the quick responses

https://spreadsheets.google.com/spr...GF0Yk55d3Q2Q2RsYy1BR2xVRU5OVWc&hl=en_US#gid=0
Not sure what "washed away" means?

If the lookup evaluates to the #N/A error then the formula returns a formula blank. The "" is an empty TEXT string.

You said you want to "hide" the #N/A errors. The formula syntax I suggested will ELIMINATE them which is kind of a way to hide them.

And yes, you'd have to modify each of your lookup formulas in that way to eliminate/hide the #N/A errors.
 
Upvote 0
Not sure what "washed away" means?

If the lookup evaluates to the #N/A error then the formula returns a formula blank. The "" is an empty TEXT string.

You said you want to "hide" the #N/A errors. The formula syntax I suggested will ELIMINATE them which is kind of a way to hide them.

And yes, you'd have to modify each of your lookup formulas in that way to eliminate/hide the #N/A errors.

ok, i'll try it, maybe i used some other similar formula or i made a mistake. thanks.
 
Upvote 0
Ok, you are right, this formula seems to be working. I will test it some more on the rest of the spreadsheet but i am optimistic that it should do it;).

Thanks very much guys for your time and effort, i hope i don't have to come back for this:)
 
Upvote 0
Ok, you are right, this formula seems to be working. I will test it some more on the rest of the spreadsheet but i am optimistic that it should do it;).

Thanks very much guys for your time and effort, i hope i don't have to come back for this:)
Good deal. Thanks for the feedback! :cool:
 
Upvote 0

Forum statistics

Threads
1,224,564
Messages
6,179,544
Members
452,925
Latest member
duyvmex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top