SUM, IFERROR and COUNTIF

I'll try to be as precise as possible.

I have a spreadsheet 10X50 at the end of each row I get some info from 10 results by using the formula:

=IF(ISERROR(SUM(A26:BR26) / COUNTIF(A26:BR26, ">0")),0,(SUM(A26:BR26) / COUNTIF(A26:BR26, ">0")))

wich serve the purpose of eliminating the #div/0 error when doing the average so I either have an amount or a 0. SO far everything works

I have named the 50 cells containing that formula "Q1.2ave" then I want to get the average of those named cells using a similar formula:

IF(ISERROR(SUM(Q1.2ave) / COUNTIF(Q1.2ave, ">0")),0,(SUM(Q1.2ave) / COUNTIF(Q1.2ave, ">0")))

and that's where I always get the error #VALUE

I have also tried Various other formula but still get the #VALUE error:

=SUM((Q1.2ave) / (IF(ISERROR(COUNTIF(Q1.2ave, ">0")),0,COUNTIF(Q1.2ave, ">0"))))

=AVERAGE(IF(Q1.2ave<>0,Q1.2ave))

=COUNTIF(Q1.2ave,NOT( ISERROR(Q1.2ave))) // to try to check where the error is

=SUMIF(Q1.2ave, ">0")


anyone can help me with that? thank you in advance

here is the refer to string of Q1.2ave:

=Données!$BZ$24,Données!$BZ$40,Données!$BZ$56,Données!$BZ$72,Données!$BZ$88,Données!$BZ$104,Données!$BZ$120,Données!$BZ$136,Données!$BZ$152,Données!$BZ$168,Données!$BZ$184,Données!$BZ$200,Données!$BZ$216,Données!$BZ$232,Données!$BZ$248,Données!$BZ$264,Données!$BZ$280,Données!$BZ$296,Données!$BZ$312,Données!$BZ$328,Données!$BZ$344,Données!$BZ$360,Données!$BZ$376,Données!$BZ$392,Données!$BZ$408,Données!$BZ$424,Données!$BZ$440,Données!$BZ$456,Données!$BZ$472,Données!$BZ$488,Données!$BZ$504,Données!$BZ$520,Données!$BZ$536,Données!$BZ$552,Données!$BZ$568,Données!$BZ$584,Données!$BZ$600,Données!$BZ$616,Données!$BZ$632,Données!$BZ$648,Données!$BZ$664,Données!$BZ$680,Données!$BZ$696,Données!$BZ$712,Données!$BZ$728,Données!$BZ$744,Données!$BZ$760,Données!$BZ$776,Données!$BZ$792,Données!$BZ$808

You won't be able to use a non-contiguous named range with COUNTIF or SUMIF or the other formulas......

I'm not sure about the legitimacy of averaging averages (that's normally not a good idea unless you have the same number of values averaged for each average) but having said that try this formula for your average of averages....

=IFERROR(AVERAGE(IF(MOD(ROW(Données!$BZ$24:$BZ$808)-ROW(Données!$BZ$24),16)=0,IF(Données!$BZ$24:$BZ$808>0,Données!$BZ$24:$BZ$808))),0)

confirmed with CTRL+SHIFT+ENTER

or you could make the original formula for each average return a blank instead of zero in case of error, i.e. use

=IFERROR(AVERAGE(A26:BR26),"")

then you could just do a straight average, i.e.

=AVERAGE(Q1.2ave)

Ok I tried changing the original formula to:

=IFERROR(AVERAGE(A26:BR26),"")

problem with that is that it doesn't ignore the 0 if there are any

tried to tweak it to something like:

=IFERROR(AVERAGE(IF(A26:BR26<>0, A26:BR26)),"")

but I automatically get a blank even if some of the cells contain other number than 0

forgot to mention, I do get the right result on ctrl + shft + enter

but only a blank without it

Originally Posted by Jomathr

Ok I tried changing the original formula to:

=IFERROR(AVERAGE(A26:BR26),"")

problem with that is that it doesn't ignore the 0 if there are any

tried to tweak it to something like:

=IFERROR(AVERAGE(IF(A26:BR26<>0, A26:BR26)),"")

but I automatically get a blank even if some of the cells contain other number than 0

Sorry, I forgot you wanted to ignore zeroes. The second of those should work, although it's an "array formula" so it needs to be confirmed with CTRL+SHIFT+ENTER.....or use AVERAGEIF as Martin suggests, i.e.

=IFERROR(AVERAGEIF(A26:BR26,">0"),"")

Alright I think I can solve the problem by moving things around, since I still get #value error, I'll try to make it contiguous instead but still can't figure out why it's doing that thx for the help all of you