# Removing the #value return in a formula

#### PeterDavids

##### New Member
Hi...

Please would anyone be able to assist in solving a pesky problem that I can't seem to master?

My formula is sum(A7:Q7)-H7 which returns a #value;

In which H7 is =VLOOKUP(A7,'[ATN Colesberg Timesheet.xlsm]APR 2013'!\$B\$8:\$CJ\$57,6,FALSE)

Will really appreciate the help!

Thanks
Peter

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
If your value error comes from the H7 cell, maybe just do the trick like that :
=sum(A7:P7,R7:Q7)

if any of the values is an error, SUM would definitely be an error.. try =SUMIF(A7:Q7,"<>#VALUE!")..

Hi Peter,

Problem is because of H7 as jacqueslegrand said. H7 cell does not contain a numerical value.

If H7 must have a numerical value you should check H7 or lets say your formula is in cell R7 write this =IF(ISERROR(H7);"Check h7";H7) than you can check find the problem.

If H7 doesnt have to have a numerival value and you want a value or something and easy iferror will solve your problem. =IFERROR(H7;"N/A")

Please let me know if you need any further assitance.

Thanks Jacques

Maybe i should provide a little more clarity.

The contents of the range A7 to Q7 is also a return of the formula =VLOOKUP(A7,'[ATN Colesberg Timesheet.xlsm]APR 2013'!\$B\$8:\$CJ\$57,6,FALSE)
where the 6 in the formula is replaced with relevant column numbers of the target spread sheet,

the formula works fine when the return of the above formula has a value in it but as soon as it contains no value, i get the error message.

Does this help a bit more?

Replies
7
Views
181
Replies
2
Views
1K
Replies
3
Views
99
Replies
6
Views
850
Replies
5
Views
677

1,221,498
Messages
6,160,161
Members
451,627
Latest member
WORBY10

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