Back to Forms in Excel VBA archive index

Back to archive home

I'm using Excel 2000 to create a monthly financial consolidation worksheet. The source data is created from an Excel worksheet that imports data from a Unix software application.

If there is no source data in a cell from the extract, then the cell is "blank" (similar to using the spacebar to populate the cell). I use the ISERROR function to satisfy formula the first referenced cell that calls the extract data (with 0 as the first criteria if the original cell is blank) Using 0 does not create a number format or populate the field, it remains blank. Otherwise I get the value from the extract data source,which populates the data into my analysis correctly....

Now I want to Sum the data to create totals. When I sum the data fields, if a "blank" field exists I get the #VALUE! error message. I've tested the formats to find that I need to remove the format from the source data (written by a 3rd party developer).

I need to identify a formula function that will either ignore the "blank" format and sum the remaining cells or a macro to reformat the "blank" imported cells without disturbing the other data.

I am automating this to be a monthly process, so a manual re-format will not be an option.

Please e-mail any suggestions that will resolve this issue. Thanks!

Check out our Excel Resources | ||||

You could use an array formula along the lines of =sum(if(Sum_range="",0,Sum_range)).

Gary

Great Idea Gary, but didn't work. The data I want to sum is not in continuous rows. I tried to sum the individual cells in your formula, still got the #VALUE! result. Added IF(ISERROR... function and only got a 0 as a result. Need sum of data.

Thanks...

Thanks...

Ted --

Normally, a cell containing an ordinary space or an empty cell shouldn't cause a #VALUE! error, becasue SUM will ignore such a cell.

Care to post the formula that you use?

Aladin

Tedd --

I don't understand what you mean by "not in continuous rows". However, would you check whether the following will work?

=SUMPRODUCT((ISNUMBER(A1:A100)+0),(A1:A100))

Adapt the range to your situation.

And, would you evaluate the following formula next to a cell that gives the trouble?

=CODE(the-cell-that-gives-trouble)

Aladin

===========

Here's the formula:

IF(ISERROR(+e11+e16+e29)="",0,+e11+e16+e29)where e29 formula is IF(ISERROR(+WK1!d45),0,+WK1!d45).

In this example, e29 returns a blank cell resulting from WK1!d45 being a blank cell.

If I clear the format in WK1!d45 or place a number in WK1!d45, then the formulas return the correct answer.

Ted--

> IF(ISERROR(+e11+e16+e29)="",0,+e11+e16+e29)where e29 formula is IF(ISERROR(+WK1!d45),0,+WK1!d45).

I think the following formula should simply work.

=SUM(E11,E16,E19)

where e19 is simply =WK1!d45 if this WK1 cell is numeric or otherwise contains a space as you stated earlier.

You can check what is in WK1!d45 when it's non-numeric by using

=CODE(WK1!D45)

Aladin

===========

This archive is from the original message board at www.MrExcel.com.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.