Sum visible cells of a filtered range ignoring errors

{ 4 Comments }

In a multi column database, assume a filter has been applied on some columns.  Post filtering, some error values (such as #DIV/0!, #NA etc.) appear in the one of the numeric columns.   The objective is to sum numbers in a numeric column.  The usual SUM() function would not work because the range of sum function should be error free.  Furthmore, the SUM() function would also include the invisible rows of a filtered range.  The SUBTOTAL(9,range) function would not work either because the range in the SUBOTAL() function should also be error free.

Assume that the database is in range A11:B20.  Column A has been filtered and column B is the column of numbers which also has the error values.

For Excel 2007 and prior versions, the following array formula (confirmed by Ctrl+Shift+Enter) will sum the visible rows a filtered range ignoring error values.

=SUM(IF(NOT(ISERROR(SUBTOTAL(109,OFFSET(B10,ROW(B11:B20)-ROW(B10),)))),SUBTOTAL(109,OFFSET(B10,ROW(B11:B20)-ROW(B10),))))

For Excel 2010 and higher versions, the following formula will work

=AGGREGATE(9,7,B11:B20)

Leave a Comment

Your email address will not be published.

*