CODEWITHSUNDEEP
excelexcel-formulavba
Navron
Navron

Reputation: 145

Sum Filtered Values Excel

I am finding the sum of filtered values while ignoring #DIV/0! errors. As such, I combined the following formulas:

=SUBTOTAL(9,$I$2:$I$349)

and 

=SUMIF($I$2:$I$349,"<>#DIV/0!")

To get:

SUBTOTAL(9,SUMIF($I$2:$I$349,"<>#DIV/0!"))

However, excel returns an error. Any insights? Is it that I cannot place a formula within another formula?

Upvotes: 2

Views: 626

Answers (4)

barry houdini
barry houdini

Reputation: 46331

Which version of Excel are you using?

In Excel 2010 and later versions AGGREGATE function works a little bit like SUBTOTAL - it can perform various functions while ignoring hidden rows.......but it also has options to ignore error values, so without any helper columns you can use this single formula

=AGGREGATE(9,7,I2:I349)

9 denotes "sum" as per SUBTOTAL while 7 as the 2nd argument indicates that error values and hidden values should be ignored. There are other options, see Excel Help for more

Upvotes: 2

Julie Routley
Julie Routley

Reputation: 1

=IFERROR(SUBTOTAL(9,$I$2:$I$349),"")

Upvotes: 0

guitarthrower
guitarthrower

Reputation: 5834

I found this bit of brilliance with some digging on the webs: http://www.excelbanter.com/showthread.php?p=760730

It's a difficult formula to follow, but should do the trick.

{=SUM(IF(ISNUMBER($I$2:$I$349),$I$2:$I$349)*(SUBTOTAL(3,OFFSET($I$2:$I$349,ROW($I$2:$I$349)-MIN(ROW($I$2:$I$349)),0,1))))}

It's an array formula, so make sure to press Ctrl + Shift + Enter (instead of just Enter).

I can't pretend to understand it all without really digging into it, but it works!

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Use a helper column

In H2 enter:

=IFERROR(G2,"")

and copy down and then use:

=SUBTOTAL(9,$H$2:$H$349)

Upvotes: 0

Related Questions