Using "sum(if(iserror" with the Subtotal formula

[taketwo]

Does anybody know how I could combine the formulas below so that I can use the "Subtotal" feature without any "#N/A" errors messing-up the sum?

=SUBTOTAL(9,LI15:LI18)

=SUM(IF(ISERROR(LI15:LI18)," ",LI15:LI18))

Thanks in advance.

[sweep]

Hi,

Why not just use the array you've mentioned?

=SUM(IF(ISERROR(LI15:LI18)," ",LI15:LI18)) confirmed with CRTL, SHIFT and ENTER?

[NBVC]

Try:

=SUM(IF(ISNUMBER(L15:L18),(SUBTOTAL(9,OFFSET(L15:L18,ROW(L15:L18)-MIN(ROW(L15:L18)),,1)))))

confirmed with CTRL+SHIFT+ENTER

[sweep]

Guys,

Am I missing something here, won't subtotal(9, include hidden values the same as a regular formula?

Dave

[taketwo]

Thanks, NBVC - no clue how it works, but it does, so I won't question it.

And Sweep, I'm with you, all of the information I've read seems to indicate that I should be using "Subtotal(10X" instead of "Subtotal(X", but it has always ignored the hidden values for me when I choose to Filter, so I'm not gonna try to fix what isn't broken.

Maybe "Hidden" and "Filtered" are considered two different things? Not a clue.

[NBVC]

Subtotal() excludes hidden values after filtering... I assume the OP is filtering...

9 is hidden only by filtering
109 is hidden by filtering and/or manually.

[NBVC]

I basically took the concept described in the section:

Count Visible Items in a Filtered List found here: http://www.contextures.com/xlFunctions04.html

and adapted it to sum based on your criteria.

[DonkeyOte]

The downside to this approach is of course the Volatility

If you were working with bigger ranges you may find it makes sense to use adjacent cells to your range, eg:

Please Login or Register  to view this content.

then run your earlier (standard) SUBTOTAL against LJ11:LJ15 rather than LI11:LI15

[taketwo]

Thanks for the help, everyone!