Total multiple cells but ignore any of them that may return errors

[KingdomBuilding]

Hi,

I'm new to this place as a user, but always reading up on other threads.

I've got a cell I'm trying to use to total multiple cells (maybe 30?) from a pivot-table together. They are not a range so I don't believe a sum function will work. However sometimes, depending on the data being used (ie YEAR), not all cells in the formula are used so the whole cell returns #REF!

It may only be one cell out of many that are "hidden" or "not available", but I still would like it to add all of the working cells together ignoring any "GETPIVOTDATA" values that don't currently exist in the "FILTERED YEAR" for example. Any thoughts?

 

[hippiehacker]

try

=SUMIF(A1:A6,"<>#REF!")

adjust tghe range to your needs

 

[KingdomBuilding]

I've seen this suggestion before, but if I'm not mistaken isn't this used in a "SUM" situation? My data is scattered all over the pivottable.

 

[Jonmo1]

They are not a range so I don't believe a sum function will work.

If this is the case, and you're trying to add several non adjescent cells like A1, D5, G2, C3 etc..
Then it's not as simple as you might think.

Might be best off correcting the GETPIVOTDATA formulas so that they don't return errors.

If you have XL2007 or higher, it's pretty easy..
=IFERROR(GETPIVOTDATA(.....),0)

Now it returns 0 instead of #REF! Errors. so you can do A1+D5+G2+C3

 

[KingdomBuilding]

I think you're on the right track. Let me see if I can explain it in more detail though.

I have a pivot table that looks at 25,000 rows of data; that data has been summed into "accounts" / categories in the pivot table. The pivot table is filtered by the "Report Filter" and the YEAR (ie 2012) is used. The data source contains several years of that data.

However, here is where the issue is. In 2011 I may have several "accounts" / categories that have summed totals, but those same accounts may not exist in 2012. I don't want to delete references to my accounts, because they may appear in 2013 again and I need the calculated cell to given me a sum total based on the year selected in the pivot table. Does this make sense?

I'm sorry this may seem unique. If you saw what I was doing it would likely make sense. I am trying to build a template for our organization to use to fill out an Income Tax Return called Form 990 (not easy).

Thanks so much