CODEWITHSUNDEEP
excelconditional-statements
E.Cross
E.Cross

Reputation: 2137

Excel Summing specific cells

I want a single cell to contain the sum of the cells adjacent to cells with a #DIV/0! enter image description here

So see how I can get the total % I have accumulated, but I also want to add the % that I have not yet had a grade for (which are the cells with #DIV/0! and account it in under the "missing (%)" column. Basically the sum of every cell left of a cell in column E that contains #DIV/0!. Any suggestions or ideas? I am very inexperienced with excel and only know basic formulas.

I have tried something like =SUMIF(D3:D16,ISERROR(E3:E16)) to no avail. (The values start in row 3)

Upvotes: 0

Views: 247

Answers (2)

chris neilsen
chris neilsen

Reputation: 53126

While I agree that avoiding the #DIV/0!'s is best, the sum can be done as is.

Try this formula, entered as an Array Formula (complete with Ctrl-Alt-Enter)

=SUM(D3:D16*ISERROR(E3:E16))

BTW, if using Excel 2007 or later, to avoid errors you should use

=IFERROR(A1/B1,"Value you want instead of error")

Upvotes: 1

Matt
Matt

Reputation: 1198

My suggestion would be to avoid generating the #DIV/0 errors by using a formula like: =IF(ISERROR(A1/B1),0,A1/B1) in column E. This will return A1/B1 if there is no error, otherwise it will return 0. (You could also print -1 to identify the errors) Once you have no errors in the column, you should be able to use SUMIF.

Upvotes: 1

Related Questions