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)
Sum visible cells of a filtered range ignoring errors
{ 4 Comments }