Ever encountered #DIV/0 problems with COUNTIF

  • Thread starter Thread starter Frank Kabel
  • Start date Start date
F

Frank Kabel

Hi all

during a discussion in the German NG I provided the following function
for counting unique entries in a range without counting blank rows:
=SUMPRODUCT((A1:A30<>"")/COUNTIF(A1:A30,A1:A30&""))

I think Harlan was the first one to post this (IMHO excellent)
solution. I had never problems with this formula but one regular in the
German NG stated the following problem:
- create a new, blank standard workbook
- enter the following:
A1: x
A2: y
A3: x

in B1 enter the formula from above.
Doing this in my workbook works just fine though he (and I definetly
believe him) encountered a #DIV/0 error returned from this formula.
Only after entering values in the range A4:A30 and afterwards clearing
them the error disappeared!

Has anybody else encountered this problem?
And even more important: Does anybode know the cause (we sepculated
about a problem of COUNTIF in combination with calculating the
usedrange..)
 
Happens to me in both 2000 and 2002, but not in 2003, exactly as you stated,
including entering values and clearing them to get the formula to work.
 
LOL - Now start with a virgin sheet, enter your formula first and you'll get a
0, and then start entering values from A1 down.

A1 with a value - Formula gives 1
A2 with a value - Formula gives 2
A3 with a value - Formula gives #DIV/0
 
Hi
and as an addition:
- the error did occur on Excel 2000 / German version
- The same, saved file opened by me in Excel 2003 / German works just
fine
 
OK, there seems to be some correlation with the usedrange in this:-

If I put the formula in and then put data in Col A until it gives #DIV/0 (Assume
cell A3), then go past that cell with more values but delete the cell that
triggered the #DiV/0 then it's OK - assume I went down to cell A5 this time. I
now clear all the data and start filling in the cells again, and this time when
I hit A5 - Bang. Go past it with data, say to A8, delete A5 and all is fine.
Repeat the exercise and this time it will bomb out on cell A8 and so on.
 
Frank

In an earlier thread in NewUsers on Counting Unique Items you used this
formula.

I tried it and got the #DIV/0 error on a newly inserted sheet in an existing
workbook.

Was going to post back with the results, but got busy and did not follow up.

Just tried it again now. Copy and paste the formula to a new sheet or new
workbook and get the error until I fill all 30 cells with values and blanks
interspersed.

Error goes away at that point. Delete all values and re-enter with no error.

Also delete sheet, insert new sheet, paste formula again and no error.

Delete sheet, save/close the workbook, re-open, insert sheet, paste formula
and error again.

Reason.........???

Gord
 
OK, figured out it was dying on A2 each time when I did it because I was
starting off putting the formula in C2, hence last row of the used range.
Starting the sheet from fresh and putting the formula in any row greater than
the last one referenced by the formula means that you can fill the values in
with no problems, but the second you hit the limit of the usedrange within the
range referenced by the formula, then #DIV/0 it is.
 
Ken said:
OK, there seems to be some correlation with the usedrange in this:-

If I put the formula in and then put data in Col A until it gives
#DIV/0 (Assume cell A3), then go past that cell with more values but
delete the cell that triggered the #DiV/0 then it's OK - assume I
went down to cell A5 this time. I now clear all the data and start
filling in the cells again, and this time when I hit A5 - Bang. Go
past it with data, say to A8, delete A5 and all is fine. Repeat the
exercise and this time it will bomb out on cell A8 and so on.
Click to expand...

Hi Ken
thanks for testing - very, very strange (maybe a bug MS corrected in
Excel 2003).
So in this case one can't rely on this formula (at least for Excel
200,2002)

Frank
 
Gord said:
Frank

In an earlier thread in NewUsers on Counting Unique Items you used
this formula.

I tried it and got the #DIV/0 error on a newly inserted sheet in an
existing workbook.

Was going to post back with the results, but got busy and did not
follow up.

Just tried it again now. Copy and paste the formula to a new sheet
or new workbook and get the error until I fill all 30 cells with
values and blanks interspersed.

Error goes away at that point. Delete all values and re-enter with
no error.

Also delete sheet, insert new sheet, paste formula again and no error.

Delete sheet, save/close the workbook, re-open, insert sheet, paste
formula and error again.

Reason.........???

Gord
Click to expand...

Hi Gord
seeing Ken's post the usedrange calculation within COUNTIF seems to
cause this problem if comparing blabk/empty cells within COUNTIF

Sounds like a bug for me

Regards
Frank
 
This reminds me of:
http://www.mrexcel.com/board2/viewtopic.php?t=72674&postdays=0&postorder=asc&start=0

XL 2000:

In B1 on a clean sheet enter the formula:

=SUMPRODUCT((A1:A30<>"")/COUNTIF(A1:A30,A1:A30&""))

Result: 0

In A1 enter: x

Result: #DIV/0!

Now add the following code:

Function Used(r As Range) As Range
'
' Harlan Grove
' Sun 25 Nov 01
'
Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(r.Parent.Cells(1, 1), q))
End Function

In B1 on another clean sheet enter the following formula:

=SUMPRODUCT((used(A1:A30)<>"")/COUNTIF(used(A1:A30),used(A1:A30)&""))

Result: 0

In A1 enter: x

Result: 1

There is definitely something with CountIf and the way used range is
invoked/applied if at all.
 
I first saw this formula
in one of J.E. McGimpsey's posts so maybe there are no problems with
Mac Excel (I believe J.E. is the originator)
I guess it's back to

=SUM(IF(A3:A30<>"",1/COUNTIF(A3:A30,A3:A30)))

again
 
Back
Top