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

=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

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"

=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?

Thanks in advance!

Joe

Re: sum including blank cells using ISERROR

Quote from Trebor76;696629

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

Display More

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

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