Share this content
10

excel formula using countif

Help to correct an excel formula

Didn't find your answer?

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)

Please login or register to join the discussion.

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)
Replying to Tim Vane:
avatar
By Henri-Hound
27th May 2016 15:59

Thank you Tim I will give it a go.

Thanks (0)
Replying to Tim Vane:
avatar
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)
Replying to NomDePlume:
avatar
By NomDePlume
27th May 2016 16:10

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

Thanks (0)
avatar
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)
Replying to Dib:
avatar
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)
avatar
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)
avatar
By adamburns
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)
Replying to adamburns:
avatar
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)
avatar
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)
Share this content

Related posts