10

# excel formula using countif

Help to correct an excel formula

• ### CAA 2001 S266 election/Cash basis

Hi I wondered if someone out there could give me the solution to an error with the following formula. =Sum(e13:e86)/COUNTIF(e13:e86,">0") I have been asked to look at a spreadsheet which calculates percentages but when there is not data in column the answer doesnt come out as 0 it is displayed as DIV/O! My knowledge of spreadsheets is quite good but not enough to solve the problem can anyone help me with my dilemma?

### Replies (10)

By Tim Vane
27th May 2016 15:30

That's a divide by zero error.

Change the formula to:

=Sum(e13:e86)/MAX(COUNTIF(e13:e86,">0"),1)

Thanks (0)
By Henri-Hound
27th May 2016 15:59

Thank you Tim I will give it a go.

Thanks (0)
By NomDePlume
27th May 2016 16:07

However, that would not provide the correct answer. I prefer the result to be blank if Div0 error results, eg +if(countif(e13:e86,">0")=0,"",sum(e13:e86)/countif(e13:e86,">0")).
On the other hand, the OP stated the case where there is not data, so an easier solution would be +sum(e13:e86)/counta(e13:86).

Thanks (0)
By NomDePlume
27th May 2016 16:10

Apologies, that would also require an if=0 precursor to counta

Thanks (0)
By Dib
27th May 2016 16:13

obviously not got the whole story but that formula won't give a percentage it will give an average won't it? Unless the cells in the range may contain 0 or a negative number then using count would produce the same effect as count ignores cells which are empty.

Thanks (0)
By NomDePlume
27th May 2016 16:39

If the entries in the range are percentages, then the result of the formula would indeed be the average percentage. Perhaps that is what the OP is after? If it isn't, then a little more information is needed from the OP.

Thanks (0)
By BrianNicholls
27th May 2016 16:14

I'm not sure what you are trying to do here. If your data contains 10, 20, and -40, do you really want an answer of -5? You are summing up all figures but only dividing by the number of positive figures.

Thanks (0)
17th Jun 2016 14:17

In my opinion the easiest method to remember is using the Iferror function.

=iferror(Sum(e13:e86)/COUNTIF(e13:e86,">0"),0)

Thanks (0)
By gogabz51
17th Jun 2016 20:30

I'd agree with adamburns, IFERROR seems to be the answer (when it's a choice between a calculation & zero, IFERROR is generally simpler than an IF statement) but, as mentioned by a number of the other respondents, I'm not entirely sure what's required here.

Thanks (0)
By JulianHalton
17th Jun 2016 14:35

Try IFERROR instead, a neater way of cleaning up the results of formulas when they try to divide by zero.
=IFERROR(formula,value-if-error)

=IFERROR(Sum(e13:e86)/COUNTIF(e13:e86,">0"),"")

Thanks (0)