# sum including blank cells using ISERROR

• Hey all...

I am working on a project for my wife. It is a sales tracking sheet of some sort. She got confused so I took over for her, now I went from basic formulas to complex, now I got myself confused.

There is a Sheet called "Sales Metrics" and one called "Monthly Totals"... this is where I got myself confused.

On the monthly sheet, I read cells from the "Sales Metrics" sheet to add together and then give a total for the month.

I know there are going to be errors, because I am pre-filling out the formulas for the next year, thus info has not been entered, it returns an error #ERROR or a #VALUE sometimes.. here is my formula...

Code
``=IF(ISERROR('Sales Metrics'!B78+'Sales Metrics'!B85+'Sales Metrics'!B92+'Sales Metrics'!B99+'Sales Metrics'!B101:B102),"-",=sum('Sales Metrics'!B78+'Sales Metrics'!B85+'Sales Metrics'!B92+'Sales Metrics'!B99+'Sales Metrics'!B101:B102))``

I have been messing with it, so somethings might be out of whack... but here is what it is trying to do...

IF Sales Metrics Cells B78, B85, B92, B99 and then the two cells B101 and B102 (which would be today Monday the 30th and Tomorrow Tuesday the 31st)

The sales guy is waiting to fill in data until I get it fixed, so with out the data, it errors, but if I plug in data, it works (or at least it did)

What the code I am using is trying to do is emulate this site help

Code
``http://office.microsoft.com/en-us/excel-help/hide-error-values-and-error-indicators-in-cells-HP003056121.aspx``

I tried plugging in my code into the "old code" area of their example, but like I said it keeps erring, so I kept moving ( ) around and who knows, I may have messed it up.

This is the "old code"

Code
``=sum('Sales Metrics'!B78+'Sales Metrics'!B85+'Sales Metrics'!B92+'Sales Metrics'!B99+'Sales Metrics'!B101:B102)``

That error'd too because all the data in the month of December is not filled out.. thus it is trying to add blank cells...

Ideas?

Joe

• Re: sum including blank cells using ISERROR

Hi Joe,

You don't say what version of Excel you're using but for 2003 and prior try this...

=IF(ISERROR('Sales Metrics'!B78+'Sales Metrics'!B85+'Sales Metrics'!B92+'Sales Metrics'!B85+SUM('Sales Metrics'!B101:B102)),0,'Sales Metrics'!B78+'Sales Metrics'!B85+'Sales Metrics'!B92+'Sales Metrics'!B85+SUM('Sales Metrics'!B101:B102))

...while for 2007 and later try this:

=IFERROR('Sales Metrics'!B78+'Sales Metrics'!B85+'Sales Metrics'!B92+'Sales Metrics'!B85+SUM('Sales Metrics'!B101:B102),0)

Kind regards,

Robert

• Re: sum including blank cells using ISERROR

Robert,

Thank you, I will try that tomorrow. It is actually Google, docs, excel.. its pretty interesting to work with when you are accustomed to working with real Excel.

I will let you know! Thanks again!

Joe

• Re: sum including blank cells using ISERROR

Robert,

Decided to give it a quick go.. the first one you posted for the 2003 at least, worked.. well it posted 557. I will see where that number came from, but if it is YTD (year to date) then that would be more than excellent!

Thanks again,

Joe

• Re: sum including blank cells using ISERROR

Hi Joe,

I've actually referenced cell B85 twice in my formulas - change these to B99 and all should be good i.e. try these instead:

=IF(ISERROR('Sales Metrics'!B78+'Sales Metrics'!B85+'Sales Metrics'!B92+'Sales Metrics'!B99+SUM('Sales Metrics'!B101:B102)),0,'Sales Metrics'!B78+'Sales Metrics'!B85+'Sales Metrics'!B92+'Sales Metrics'!B99+SUM('Sales Metrics'!B101:B102))

=IFERROR('Sales Metrics'!B78+'Sales Metrics'!B85+'Sales Metrics'!B92+'Sales Metrics'!B99+SUM('Sales Metrics'!B101:B102),0)

Cheers,

Robert

• Re: sum including blank cells using ISERROR

Robert,

Ok.. that came out to 338..

Ok.. decided to run the numbers.. and you are spot on...

78 = 119
82 = 219
92 = 0
99 = 0
101 & 102 = 0
-----------------
338

Spot on! Great job, thank you so much for your help!

Joe

• Re: sum including blank cells using ISERROR

Hi Joe,

Thanks for letting us know and you're welcome. Just note that the ISERROR formula I provided is suitable for all versions of Excel not just 2003 and prior, though I personally prefer the 2007 formula.

Have a great 2014.

Regards,

Robert

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!