Sum formula not working with #n/a cells.

**bmonaweck02** · 09-11-2011, 03:28 PM

I am creating a blank billing sheet and I have got a couple of different formulas in there. I am trying to use a sum formula at the bottum to sum the totals of labor and equipment. The sum won't total if there are cells with the #n/a. What I've got is a VLOOKUP formula to pull up the rate and then a HRS Worked column. The rate and HRS Worked multiply to give a labor total for one person. But, If there is no classifiacation the VLOOKUP cannot bring up a rate and therefore cannot put a labor total in for that person. I have a labor total at the bottum for all of the employers. That will not total if there is one cell with out a total for one employee. If there is not a total for an employee it will show up as #n/a because the formula cannot total for that employee.

I would think that there is a way that you can sum the column even if there is a #n/a cell in the column.

Any help is greatly appreciated.

Thanks,
Brian

**MarvinP** · 09-11-2011, 03:41 PM

Hi bmonaweck02 and welcome to the forum,

Fix the formulas that give the #N/A so they display zero instead.

If they are like:

Please Login or Register  to view this content.

Make them:

Please Login or Register  to view this content.

**daddylonglegs** · 09-11-2011, 04:12 PM

There are also some options to sum a range and ignore errors, you can sum column A ignoring #N/A errors with this formula

=SUMIF(A:A,"<>#N/A")

....or use this one to sum the same column and ignore any errors

=AGGREGATE(9,6,A:A)