1. ## #DIV/0 (2000)

Hans supplied me with this formula earlier, it works great except for the error mentioned

=SUMIF(Sheet1!\$K\$4:\$L\$160,">0")/COUNTIF(Sheet1!\$K\$4:\$L\$160,">0")
I tried

=SUMIF(ISERROR(Sheet1!\$K\$4:\$L\$160,">0")/COUNTIF(Sheet1!\$K\$4:\$L\$160,">0"))
But it says that I have too many arguments. Could I be placing the end ) in the wrong place.

Thanks

2. ## Re: #DIV/0 (2000)

I just tried Hans' formula and it works fine for me. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

3. ## Re: #DIV/0 (2000)

As I said the formula works great - When some cells are blank I get the #DIV/0 I want to eliminate this...thanks

4. ## Re: #DIV/0 (2000)

You have to test separately, not within the SUMIF; moreover the parentheses are placed incorrectly in your second formula. Try this:

=IF(COUNTIF(Sheet1!\$K\$4:\$L\$160,">0")=0,"",SUMIF(Sh eet1!\$K\$4:\$L\$160,">0")/COUNTIF(Sheet1!\$K\$4:\$L\$160,">0"))

5. ## Re: #DIV/0 (2000)

Thankyou very much, I was on the wrong track.

6. ## Re: #DIV/0 (2000)

Would this work also?

=SUMIF(A1:A6,">0")/MAX(1,COUNT(A1:A6)-COUNTIF(A1:A6,0))

=SUMIF(A1:A6,">0")/MAX(1,COUNTIF(A1:A6,">0"))

7. ## Re: #DIV/0 (2000)

Thanks

#### Posting Permissions

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