How to apply a formula to visible cells only without using subtotal

[willtmc]

I am trying to apply the XIRR formula to data that I have selected by a filter. I want the result of the XIRR formula to vary based on the rows of data that are visible after being filtered. If XIRR was one of the 9 formulas supported in subtotal it would be great, but it is not. Any ideas?

Thanks,

Will

 

[mikerickson]

[hmm..trickier than I thought]

 

[mikerickson]

Try substituting this array function

{SUBTOTAL(3,INDEX(B:B,ROW(B6:B12),1))*INDEX(B:B,ROW(B6:B12),1)}

for B6:B12 in the arguments of the XIRR function.

This needs to be confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac)