# N/A Error

#### Simmo9

##### New Member
I am using the following vlookup to return a value for some run rates of production lines. however, when i update my master data' and a product line de-lists, i am left with an N/A error which corrupts further data. Can anybody help with a simple update that would return the value 0?

=IF(SUMIF(Uniq,\$A68&AU\$2,'Forecasted Requirement'!\$G:\$G)/\$E68>0,SUMIF(Uniq,\$A68&AU\$2,'Forecasted Requirement'!\$G:\$G)/\$E68+(\$P68/60),SUMIF(Uniq,\$A68&AU\$2,'Forecasted Requirement'!\$G:\$G)/\$E68)

Many Thanks
Simmo9

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Yard

##### Well-known Member
erm, that's not a VLOOKUP function?

Also, is your Uniq named range the same size as a complete column? The 1st and 3rd arguments of SUMIF must be the same dimension otherwise you'll get unexpected results.

#### Simmo9

##### New Member
Sorry - i was in the middle of writing a vlookup function!! Unable to multi task!

Thanks for the swift reply, yes, the uniq reference is the full size of a column.

forgive my ignorance, i have inherited some spreadsheets, and i'm still getting to grips with all the formulas - clearly the last guy here was more advanced than myself!

#### Yard

##### Well-known Member
It won't be very pretty to de-error that formula unless you are using Excel 2007.

Is the #N/A coming from the SUMIF or from one of \$A68, AU\$2, \$E68 or \$P68?

#### Simmo9

##### New Member
No such look, using excel '03!

It is a response to the sumif as the other arguments are fine.

#### Yard

##### Well-known Member
Then

=IF(ISNA(SUMIF(Uniq,\$A68&AU\$2,'Forecasted Requirement'!\$G:\$G)),0,IF(SUMIF(Uniq,\$A68&AU\$2,'Forecasted Requirement'!\$G:\$G)/\$E68>0,SUMIF(Uniq,\$A68&AU\$2,'Forecasted Requirement'!\$G:\$G)/\$E68+(\$P68/60),SUMIF(Uniq,\$A68&AU\$2,'Forecasted Requirement'!\$G:\$G)/\$E68))

#### Simmo9

##### New Member
Thats great, thanks.

Replies
0
Views
127
Replies
4
Views
600
Replies
5
Views
227
Replies
3
Views
278
Replies
1
Views
575

1,191,274
Messages
5,985,702
Members
439,974
Latest member
sjoerdbosch

### 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.

### Which adblocker are you using?

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

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