VBA Countif(s) Syntax issue (and/or about multiple

Question

I've looked around the Stackoverflow forums (and basically everywhere else on Google) and have found a lot of "almost" answers to my questions that would probably suffice if I was a bit more familiar with VBA, but I've messed around with it for a while and haven't been able to sort out. Getting a little frustrated, so I figured it was time to ask! Sorry if I get verbiage etc. wrong when explaining my issue! It's probably just a problem with my syntax.

Basically I need to be able to take data from a column in a spreadsheet and have it do a Countifs (I think anyway) of cells of a specific set of parameters. All of the data I need would be page dimensions with the format of "Dimension1 x Dimension 2" e.g. "8.5 x 11"

Valtest = Application.WorksheetFunction.CountIfs(Range("P:P"), "8.5 x 11")

This formula, probably rather unsurprisingly, works just fine. But I need the countifs (or whatever I need) to also be able to give me dimensions <= 8.5 x <=11 as well as flipping the dimensions (<=11 x <=8.5).

I've tried changing the formula to formats like (and similar)

Valtest = Application.WorksheetFunction.CountIfs(Range("P:P"), "<=8.5" & " x " & "11")

But that will report a dimension such as 3 x 4 or 22 x 11. I know countifs can use multiple parameters (which is why I was messing around with it instead of just a normal countif), but I don't know if entering multiple arguments is even the correct path or if it's something with quotations being used correctly or... who knows?

I was able to make an If-then statement work alright (using an array and cycling through each cell with a counter) but this is clearly not the fastest way to do this. Here it is, just for the sake of making my goal a little clearer.

'If x(0) <= 8.5 And x(1) <= 11 Or x(1) <= 8.5 And x(0) <= 11 Then

In a related issue, I'd also need to be able to find pages that are, for example, <=11 x <=17 or something while not including search results for my previous question (8.5 X 11). So I need to know the proper syntax for multiple parameters that would involve saying something like <8.5 but less >=17.

Thanks in advance! Any help is quite appreciated. Let me know if I haven't explained anything adequately.

Edit: An example of data I would be searching:

A                     Count for 8.5 x 11 (expected output)
8.6 x 11              5
8.5 x 11  
8.5 x 11  
8.5 x 11  
8.5 x 11  
8.4 x 11  
22 x 11  
10 x 17   

Answer 1

You can try this UDF: copy and paste to a regular VBA module. You pass it a range, and lower/upper bounds for the small and large dimensions respectively.

Eg: to count all sizes with small side between 8 and 10 and large side between 12 and 14 (inclusive):

=CountSizes(A:A,8,10,12,14)

EDIT: for your specific use case of 8.5x11 or smaller

=countsizes(A:A, 0, 8.5, 0, 11)  'one side btw 0 and 8.5 & one side btw 0 and 11.5   

EDIT3: to show how you'd use this from VBA instead of as a UDF, including your second column

Sub Tester()
    With ThisWorkBook.Sheets("Pages")
        'count only where second column has "Color"
        .Range("B1").Value = CountSizes(.Range("A:B"), "Color", 0, 8.5, 0, 11)
    End With
End sub

Code:

Function CountSizes(rng As Range, colorType As String, _
                     smallGE, smallLE, largeGE, largeLE)

    Dim tmp, val, v1, v2, small, large, arr, arrVals
    Dim num As Long, r As Long, nr As Long

    num = 0
    arr = rng.Value
    nr = UBound(arr, 1)
    For r = 1 To nr
        val = Trim(arr(r, 1))
        If val Like "*x*" Then
            arrVals = Split(val, "x")
            v1 = Trim(arrVals(0))
            v2 = Trim(arrVals(1))
            If IsNumeric(v1) And IsNumeric(v2) Then
                v1 = CDbl(v1)
                v2 = CDbl(v2)
                If v1 > v2 Then
                    small = v2: large = v1
                Else
                    small = v1: large = v2
                End If

                If small >= smallGE And small <= smallLE And _
                   large >= largeGE And large <= largeLE Then

                    If Trim(arr(r, 2)) = colorType Then
                        num = num + 1
                    End If

                End If

            End If
        End If
    Next r

    CountSizes = num
End Function