Sum non-consecutive cells while ignoring #DIV/0!

[BabeHeart]

I am trying to sum non-consecutive cells and ignore those with #DIV/0!. The error cells have a formula in them for which no data has yet been entered. My spreadsheet issue looks something like this:

number
number
number

subtotal

number
number
number

subtotal

TOTAL

I need to add up the subtotals and display in the TOTAL cell, but ignore the subtotals that have the error (the numbers are values by month and so some subtotals display an error until we get to that quarter of the year). I did my due diligence and searched the web for an answer but haven't come up with anything. Thanks for any help!

EDIT: There are 4 subtotals in reality, but I didn't include them all in the example (in case that matters).

 

[robertgama]

What version of excel do you have? 2010 you can use the AGGREGATE function which has an option to ignore errors.

Earlier version of Excel there are a number of ways, a couple of which are you can change your formulas to have an =IFERROR(yourformula, 0) to put 0's in those cells when there is an error.

 

[BabeHeart]

I'm sorry I neglected to include the version. It's 2010. I'm not familar with "Aggregate".

 

[robertgama]

http://www.youtube.com/user/ExcelIsFun#p/search/1/x9J2N31KTpE

 

[BabeHeart]

Thank you for the link. I don't understand how to use the function, but it was interesting to watch. My knowledge of Excel formulas is not all that advanced, and I don't always understand the 'arguments' needed.

 

[Expiry]

I would do something to the subtotals so that they display 0, instead of error.

Something like =IF(ISERROR(existingformula),0,existingformula)

or

=IF(reporting date> today(),0,existingformula).

Then sum all the subtotals.

 

[robertgama]

http://office.microsoft.com/en-us/excel-help/aggregate-function-HA010338704.aspx

You are going to do it like this:

=AGGREGATE(9,6, A1:A100)

9 means use the sum function
6 means to ignore errors
A1:A100 is the range you want to sum (put your range here)

 

[robertgama]

Sorry you would probably use option 3 instead of 6 to..

Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions.

This way it will actually sum your numbers ignoring the subtotals and errors.

 

[BabeHeart]

Thank you Expiry. I tried your first suggestion and it worked. I think the AGGREGATE would have worked also, if I could figure it out. My attempt gave me a result that was the #DIV0! error.

Happy Holidays!

 

[BabeHeart]

robertgama, I will try that also. Thank you for the explanation!