why doesn't this countif work?

J

John

NumberOfTriples = WorksheetFunction.CountIf(Myrange, "???")
The above gives NumberOfTriples as 1 which is incorrect


For Each cell In MyRange
If Len(cell) = 3 Then NumberOfTriples = NumberOfTriples + 1
Next
This one gives NumberOfTriples as 4 which is correct


The ranges are simple 3x3 or 5x5 like A1:C3

thanks

John
 
B

barry houdini

John,

What sort of data do you have in the range? COUNTIF(range,"???") wil
only count text not numbers, if you want to count cells with
characters with a formula

=SUMPRODUCT(--(LEN(range)=3)
 
B

Barb Reinhardt

In the help, it looks like it should work, but I couldn't get it to work just
in a worksheet. I did get this to work in the worksheet when entered as an
array function.

=COUNT(IF(LEN(D6:D13)=3,D6:D13))

You may want to try something like that.

HTH,
Barb Reinhardt
 
J

John

Its numbers as text. Working on sudoku solver so numbers stored as text,
at least I hope so. The cells are all formated as text and every
variable holding the data is a string variable. All string manipulations
seem to work fine such as mid, len, trim etc. when working with the cell
as a range. The Find function works with "???" but not the countif
function. However the countif works with "?". I have one range,
WholeThing, which is the entire sudoku.

If WorksheetFunction.CountIf(WholeThing, "?")=81 then the sudoku is
solved. And that works just fine.

It's not such a big deal cause there's other ways but it's just a mystery.


John
 
J

John

That's more or less what I'm doing. It's just a mystery why countif
won't work. It works with a single "?".
 
B

barry houdini

You need to be careful when you format cells as text. If you enter a
number in a cell and then format as text afterwards then the cell format
will say text.....but it isn't. You can check by using ISTEXT worksheet
function.

Usually COUNTIF counts text and numbers so

=COUNTIF(A:A,1)

will count any cells in column A that contain a 1 whether it's text
formatted or not, but as soon as you introduce a wildcard, numbers
aren't included so

=COUNTIF(A:A,"1?")

won't count 12 (unless it's text-formatted) but will count 1x
 
D

Dave Peterson

I'd check the data first and also make sure that that variable was 0 when I
started.

Maybe you have leading/trailing spaces in one of your cells

if len(cell.value) = len(trim(cell.value)) then
'ok
else
msgbox cell.address & " has an extra space!"
end if

NumberOfTriples = 0
for each cell in myrng.cells
...
 
R

Rick Rothstein

If WorksheetFunction.CountIf(WholeThing, "?")=81 then the
sudoku is solved. And that works just fine.
Click to expand...

Why not just use the CountA worksheet function? Assuming WholeThing is a Range...

If WorksheetFunction.CountA(WholeThing) = 81 Then
 
Top