CountIfs seems to be adding values together

[Seandobson2402]

Hi All,

I have the below code to count through columns BL, BM and BN on my Completed Sheet. The information entered into these columns can be - 1.1 or 1.2 or 2.1 or 2.2 or 3 or 4.

The information is then pasted into OverallStats Sheet columns C22 to E28.

Row 22 being 1.1, Row 23 being 1.2 and so on. However, my count is adding the 1.1 and 1.2 / The 2.1 and 2.2 into the same values (ie If I have 2 x 1.1 and 1 x 1.2 - It will display that I have 3 x 1.1 and 0 x 1.2)

Any idea how I could split the result out to display exact matches?

Sub FillCatTable()
Dim CatValue As Double
    
    With ThisWorkbook
        With .Worksheets("Completed")
            For CatValue = 1.1 To 4
                'get my cat stats
                ThisWorkbook.Worksheets("OverallStats").Cells(22 + CatValue, "C").Value = WorksheetFunction.CountIfs(.Range("BL:BL"), CatValue)
                'get his cat stats
                ThisWorkbook.Worksheets("OverallStats").Cells(22 + CatValue, "D").Value = WorksheetFunction.CountIfs(.Range("BM:BM"), CatValue)
                'get her cat stats
                ThisWorkbook.Worksheets("OverallStats").Cells(22 + CatValue, "E").Value = WorksheetFunction.CountIfs(.Range("BN:BN"), CatValue)
            Next CatValue
            'get my cat stats
            ThisWorkbook.Worksheets("OverallStats").Cells(28, "C").Value = WorksheetFunction.CountIfs(.Range("BL:BL"), "N/A")
            'get his cat stats
            ThisWorkbook.Worksheets("OverallStats").Cells(28, "D").Value = WorksheetFunction.CountIfs(.Range("BM:BM"), "N/A")
            'get her cat stats
            ThisWorkbook.Worksheets("OverallStats").Cells(28, "E").Value = WorksheetFunction.CountIfs(.Range("BN:BN"), "N/A")
        End With
    End With
End Sub

 

[Seandobson2402]

Tried looking with fresh eyes but can’t figure out. Will I have to write additional code to count a specific value, instead of relying on what I have?

 

[Joe4]

The information entered into these columns can be - 1.1 or 1.2 or 2.1 or 2.2 or 3 or 4.

First off, your loop will not hit these values the way you have written it.
This can be demonstrated pretty easily by showing what value each iteration returns like this:

Sub Test1()

    Dim CatValue As Double
    
    For CatValue = 1.1 To 4
        MsgBox CatValue
    Next

End Sub

The For loop has an optional "Step" component to it in which you tell it how much to jump by each iteration. If you leave it off, it defaults to 1, which is why you see those values it is returning.

You could set the step to 0.1 like this:

    For CatValue = 1.1 To 4 Step 0.1

But then you are going to go through a whole bunch of values you don't need (going from 1.1 to 4 by increasing 0.1 each time).
This is very inefficient if you do not need those other values and will hurt performance.

When you have a situation like you do, where you do not have a consistent "jump", but have a defined set of values, you can store the different values in an array and then just loop through the array like this:

Sub Test2()

    Dim CatValue As Variant
    Dim i As Integer
    
    CatValue = Array(1.1, 1.2, 2.1, 2.2, 3, 4)
    
    For i = LBound(CatValue) To UBound(CatValue)
        MsgBox CatValue(i)
    Next i
    
End Sub

So that is probably the way you want to go.

Also note: Are the values in your columns entered as numbers or text? If text, then you will probably need to reflect that in the array like this:

    CatValue = Array("1.1", "1.2", "2.1", "2.2", "3", "4")