# Shorter Formula

1. ## 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  Register To Reply

2. 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))  Register To Reply

3. ## RE: Shorter Formula

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
=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
>
>  Register To Reply