Trying to find sums with #N/A involved

[MKief]

I am currently struggling with trying to find the sums because the #N/A is involved. I have attached an example of what I am working on. Basically there is different levels of totals I am trying to get the first is the various total (the yellow box and they are grouped togethor). Second is the subtotal (blue which includes the various totals). The third is the total of all the accounts (Red). There isn't really anyway to go around the #N/A because it is pulled from a system. If there is any solution that would be great. This is just a small example and I'm dealing with a several hundred accounts.

[oeldere]

maybe with a pivot table. (see my example).

[FDibbins]

hmm could you use a helper column to pull the values from C into, say, F, to exclude the #NA, and then base your totals on that? for instance in F5...

=IF(ISNUMBER(C5),C5,"")

[mshale]

Using that sheet you posted, for the "other suptotal":

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

and you can use that for the other subtotals as well.

[martindwilson]

=SUMIF(C1:C33,"<>#n/a")

[FDibbins]

LOL i was wondering why that didnt work when i tried that at 1st, and now i just saw why...i left the / out of the N/A

[Haseeb Avarakkan]

Since you are on Excel 2010 take a look on AGGREGATE function. There are options like "Ignore error values, nested SUBTOTAL and AGGREGATE functions"