Sum(if(iserror function to remove #value error

Question

Hi,

I use the function Sum(if(error to overlook the error within a range. However, this time, I need to use same function but for multiple cells with in different tabs. For example, Sum(if(error(A1,'Tab2'!A1)," ",A1,'Tab2'!A1)). This method doesn't work since there are more than 3 arguments. Any suggestions?

Thanks,

DMN

Answer 1

Hi,

Try this

=AGGREGATE(9,6,A1,Sheet2!A1,Sheet3!A1)

Answer 2

I have 7 tabs: Beginning Inventory, Beg Property, Beg Depreciation, Ending Inventory, End Prop, End Depreciation, and Rent Expense. First, I wanted to calculate the Net Book Value which is Property at Cost minus Depreciation. Then total it with Inventory. Do the same thing for Beg and End balance. And then take average of those two. Them sum that average with Rent times 8. Normally my function would be like this:

=Average(Sum(Beg Inventory, Beg Prop - Beg Depr),Sum(End Inv, End Prop - End Depr))+Rent*8

But I kept having the #Value error probably due to blank cells. Please help. Thanks.

Answer 3

Hi,

Please share some data and describe the problem in simple English.

Answer 4

I ran into another problem. That function works if I only need to sum or average. What if I need both? Here's what I use in the meantime, but it takes too long to write the formula. How can I use aggregate here? Or is there a way to shorten this?

=IFERROR(IF(OR(ISNUMBER(J63),ISNUMBER('Detail - Beg Prop Buildings'!J63),ISNUMBER('Detail - Beg Prop Land'!J63),ISNUMBER('Detail - End Prop Inventory'!J53),ISNUMBER('Detail - End Prop Buildings'!J63),ISNUMBER('Detail - End Prop Land'!J63),ISNUMBER('Detail

• Rent Real Property'!J63)),'Detail - Rent Real Property'!J63*8+AVERAGE(SUM('Detail - End Prop Land'!J63,'Detail - End Prop Buildings'!J63,'Detail - End Prop Inventory'!J63),SUM('Detail - Beg Prop Land'!J63,'Detail - Beg Prop Buildings'!J63,'Detail - Beg Prop Inventory'!J63)),0),0)

Answer 5

Thank you so much! That works.