Shorter Formula

Can anyone shorten this formula please. Basically all it
does is gives me an average of the figures in Column "W"
depending on the number of times that product appears
in "R" column

=IF(ISERROR(SUM(SUMIF($R$5:$R$9,R62,$W$5:$W$9),SUMIF
($R$22:$R$26,R62,$W$22:$W$26),SUMIF
($R$39:$R$43,R62,$W$39:$W$43))/COUNTIF
($R$5:$R$43,R62)),0,SUM(SUMIF
($R$5:$R$9,R62,$W$5:$W$9),SUMIF
($R$22:$R$26,R62,$W$22:$W$26),SUMIF
($R$39:$R$43,R62,$W$39:$W$43))/COUNTIF($R$5:$R$43,R62))

thanks

Pete

Pete,

I'm assuming the only time you would get an error is when you try to divide by 0. The only time you would divide by 0 is when COUNTIF($R$5:$R$43,R62)=0. So you can try this equation below. It shortens it up a little bit.




=IF(COUNTIF($R$5:$R$43,R62)=0,0,SUM(SUMIF($R$5:$R$9,R62,$W$5:$W$9),SUMIF($R$22:$R$26,R62,$W$22:$W$26),SUMIF($R$39:$R$43,R62,$W$39:$W$43))/COUNTIF($R$5:$R$43,R62))

I didn't try too hard to analyze your formula, just noted that your ranges
and sum_ ranges started at row 5 and stopped at row 43. If that is so, this
does what your words say:
=SUMIF(R5:R43,R62,W5:W43)/COUNTIF(R5:R43,R62)

"Pete" wrote:

> Can anyone shorten this formula please. Basically all it
> does is gives me an average of the figures in Column "W"
> depending on the number of times that product appears
> in "R" column
>
> =IF(ISERROR(SUM(SUMIF($R$5:$R$9,R62,$W$5:$W$9),SUMIF
> ($R$22:$R$26,R62,$W$22:$W$26),SUMIF
> ($R$39:$R$43,R62,$W$39:$W$43))/COUNTIF
> ($R$5:$R$43,R62)),0,SUM(SUMIF
> ($R$5:$R$9,R62,$W$5:$W$9),SUMIF
> ($R$22:$R$26,R62,$W$22:$W$26),SUMIF
> ($R$39:$R$43,R62,$W$39:$W$43))/COUNTIF($R$5:$R$43,R62))
>
> thanks
>
> Pete
>
>