Using iferror with sumproduct for a dynamic 3Dsum

[crocco]

I’m trying to do something like an advanced 3DSum where instead of having all the tabs lined up next to eachother, it refers to a range on the sheet for which tabs to sum. I get pretty close with this:

=sumproduct(sum(indirect$AN$10:$AN$15&”!”&cell(“address”,c46))))

Where $AN$10:$AN$15 has a list of tabs, and I’m totalling cell c46 on every tab.

The issue is that if that tab name range has a blank cell or an unrecognised tab, the whole formula errors out. How to I get the error to resolve to zero for individual items in the array rather than the whole formula?

Thanks!

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(IFERROR(N(INDIRECT("'"&$AN$10:$AN$15&"'!C46")),0))

Hope this helps!

 

[crocco]

I’m pretty sure this formula won’t be dynamic, ie if I drag it then it won’t change the cell it’s adding. My original formula worked if I dragged it. The issue is that the iferror isn’t working for a single item in the array.

 

[Domenic]

In that case, you can use the Cell function, as per your original post...

=SUM(IFERROR(N(INDIRECT("'"&$AN$10:$AN$15&"'!"&CELL("address",C46))),0))

...confirmed with CONTROL+SHIFT+ENTER.

 

[crocco]

Ok that’s I’ll try that! What’s the N() function?

 

[Domenic]

In this case, INDIRECT returns an array of references. Therefore, in order to obtain the values from those references, we "de-reference" using the N() function.