# 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

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))

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
>
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1