Count multiple criteria vba excel

[DANNYOCEAN]

I have a code that sucessfully calculates how many numbers are lower than for example 45 , for large sample number list.

But i´m looking for a code that have a COUNT function with MULTIPLE CRITERIA such as, for example:

i wanna know which are the numbers and how many of them follow the MULTIPLE CRITERIA: [lower than or equal to 5 (<=5)] OR [lower than or equal to 15 AND higher than or equal to 11 (>=11 And <=15)] OR [lower than or equal to 25 AND higher than or equal to 21 (>=21 And <=25)] OR [lower than or equal to 35 AND higher than or equal to 31 (>=31 And <=35)] and so on until the final element ... OR [lower than or equal to 95 AND higher than or equal to 91 (>=91 And <=95)].
So the MULTIPLE CRITERIA is: {[lower than or equal to 5 (<=5)] OR [lower than or equal to 15 AND higher than or equal to 11 (>=11 And <=15)] OR [lower than or equal to 25 AND higher than or equal to 21 (>=21 And <=25)] OR [lower than or equal to 35 AND higher than or equal to 31 (>=31 And <=35)] ... OR [lower than or equal to 95 AND higher than or equal to 91 (>=91 And <=95)]}

i try to put the MULTIPLE CRITERIA above into my simple spreadsheet, but i didn´t have any sucess.

Can someone give a hand??

Thanks in advance

[Rabbit]

I have moved your thread to the Access forum because it uses the same programming language.

Also, this is not a code writing service but if you post the code you've tried along with a description of the problem and any error messages you get, we can help guide you to a solution.

As for your attachment, most people will not download it for security reasons.

[DANNYOCEAN]

the code that is working for only one mathematical condition is, for example numbers are lower than 45 :

Sub COUNT()
Dim ARRDATA As Variant '§ with range=>array must ALLWAYS be variant.
Dim ROWidx As Integer
Dim COLidx As Integer
'§ COLidxlect amount
Dim ARRCOUNTER() As Integer '§ count results for each line
Dim COUNTER As Integer
Dim TOTAL As Integer
'§ COLidxlect numbers
Dim ARRNUMS() As Integer
Dim ARRNUMSALL() As Integer
'§ sorting
Dim ARRNUMSidx As Integer
Dim ARRNUMSidxMax As Integer
Dim VARSWAP As Integer
Dim SWAPPED As Boolean
 
'§ put sheet1 in array
    Sheets(1).Select
'§                              ( |---------ROWs--------------|,|--------COLs----------------------| )
    ARRDATA = Range("A1").Resize(Range("A1").End(xlDown).ROW, Range("A1").End(xlToRight).Column)
'§ count each ROWidx
    ReDim ARRCOUNTER(1 To UBound(ARRDATA, 1), 1 To 1)
    ReDim ARRNUMSALL(1 To UBound(ARRDATA, 1), 1 To UBound(ARRDATA, 2))
    For ROWidx = 1 To UBound(ARRDATA, 1)
        COUNTER = 0
        ReDim ARRNUMS(0)
        For COLidx = 1 To UBound(ARRDATA, 2)
            If ARRDATA(ROWidx, COLidx) <= 45 Then
                COUNTER = COUNTER + 1
                ReDim Preserve ARRNUMS(UBound(ARRNUMS) + 1)
                ARRNUMS(UBound(ARRNUMS)) = ARRDATA(ROWidx, COLidx)
            End If
        Next
        '§ put COUNTER in ARRCOUNTER
        ARRCOUNTER(ROWidx, 1) = COUNTER
        '§ sort ARRNUMS
'    ARRNUMSidxMin = LBound(ARRNUMS)
        ARRNUMSidxMax = UBound(ARRNUMS) - 1
        Do
            SWAPPED = False
            For ARRNUMSidx = 1 To ARRNUMSidxMax
                If ARRNUMS(ARRNUMSidx) > ARRNUMS(ARRNUMSidx + 1) Then
                    VARSWAP = ARRNUMS(ARRNUMSidx)
                    ARRNUMS(ARRNUMSidx) = ARRNUMS(ARRNUMSidx + 1)
                    ARRNUMS(ARRNUMSidx + 1) = VARSWAP
                    SWAPPED = True
                End If
            Next
            ARRNUMSidxMax = ARRNUMSidxMax - 1
        Loop Until Not SWAPPED
        '§ put numbers in ARRNUMSALL
        For ARRNUMSidx = 1 To UBound(ARRNUMS)
            ARRNUMSALL(ROWidx, ARRNUMSidx) = ARRNUMS(ARRNUMSidx)
        Next
    Next
'§ copy ARRCOUNTER to sheet
    Range("Q1").Resize(UBound(ARRCOUNTER), 1) = ARRCOUNTER
'§ calculate TOTAL
    TOTAL = 0
    For ROWidx = 1 To UBound(ARRCOUNTER, 1)
        TOTAL = TOTAL + ARRCOUNTER(ROWidx, 1)
    Next
'§ put TOTAL in sheet
    Range("Q" & CStr((UBound(ARRCOUNTER, 1) + 2))).Value = TOTAL
'§ put ARRNUMS in sheet
    Range("S1").Resize(UBound(ARRNUMSALL, 1), UBound(ARRNUMSALL, 2)) = ARRNUMSALL
'§ clear memory
    ReDim ARRDATA(0)
    ReDim ARRCOUNTER(0)
    ReDim ARRNUMS(0)
    ReDim ARRNUMSALL(0)
End Sub
 

But i´m looking for a code that have a COUNT function with MULTIPLE CONDITIONS such as, for example:

i wanna know which are the numbers and how many of them follow the MULTIPLE CONDITIONS :

[lower than or equal to 5 (<=5)] OR [lower than or equal to 15 AND higher than or equal to 11 (>=11 And <=15)] OR [lower than or equal to 25 AND higher than or equal to 21 (>=21 And <=25)] OR [lower than or equal to 35 AND higher than or equal to 31 (>=31 And <=35)] and so on until the final element ... OR [lower than or equal to 95 AND higher than or equal to 91 (>=91 And <=95)].

So the MULTIPLE CONDITIONS is:

{[lower than or equal to 5 (<=5)] OR [lower than or equal to 15 AND higher than or equal to 11 (>=11 And <=15)] OR [lower than or equal to 25 AND higher than or equal to 21 (>=21 And <=25)] OR [lower than or equal to 35 AND higher than or equal to 31 (>=31 And <=35)] ... OR [lower than or equal to 95 AND higher than or equal to 91 (>=91 And <=95)]}

i try to put the MULTIPLE CONDITIONS above into my simple spreadsheet, but i didn´t have any sucess.
my excel program is 2007 with microsoft visual basic 6.3 .

Can someone help me ??

Many thks in advance

[NeoPa]

I don't believe there is a conditional function that works that way. COUNTIF() and SUMIF() both work with a criteria value that simply checks each cell in the range for the specific criteria. No way I found to specify a complicated set of criteria as there is no way to specify the left side of the test and anything more complicated than ">" or "<" results in a chack to match the string itself.

On the other hand, a much simpler approach would be to create a separate column with a formula which results in TRUE or 1 depending on the multiple criteria you require :

=OR(A1<6,AND(A1>10,A1<16),AND(A1>20,A1<26))

This is just an illustration of what you could use to handle those matching values up to 25. You would need to fill in the extra criteria yourself.

A formula to count all occurrences could be put at the bottom of that column such as :

=COUNTIF(X1:X?,TRUE)

where X is the column letter and ? refers to the bottom row containing data.

PS. A shorter formula might be :

=AND(VALUE(RIGHT(A1,1))>0,VALUE(RIGHT(A1,1))<6)

This handles all values less than 100.

[zmbd]

CountIf (v2003 earlier) can not be used with multiple; however, there are a few workarounds:
COUNT Using Multiple Criteria in Microsoft Excel
CountIf Function (basic Information)
COUNTIFS function (v2007)

If you have to have a VBA solution:
I think you've over thought the solution. I would use five variables, a select case, and a for each cell loop within the selected range to step thru the cells and trip a count.
Might I suggest that you take a few momemnts and do a logic chart? I prefer Nassi–Shneiderman diagrams which should make the code easier anyway. It's quite late here so, once I get a nap, I may take a closer look... just seems to be a good solution.

And a final offering if a VBA isn't required, or at least until you can develop a VBA solution: you might take a look at the historgram function in the data analysis add-in: How to use the Histogram tool in Excel Chose the "PareTo (Sorted Histogram)" option instead of the chart.

I use this all of the time.
For the bin values are going to be your tricky part...
Your criteria are just a tad all over the place.

However, just as a simple example: if you set the bins to say: 5,10,15,20: then what you get is all of the numbers less than and equal to each bin (x<=5, 5<x<=10, 10<x<=15, 15<x<=20, 20<x) - the link above has example data so I won't duplicate that here.

As for how you presented your criteria... it is increadibly difficult to determine what you are doing! I've read thru it 6 times and it's starting to make my head hurt - it could also be that I've been up for a very long time too. You need to provide some formating and grouping.

The best I've come up with is:

{[lower than or equal to 5 (<=5)] 
   OR [lower than or equal to 15 
      AND higher than or equal to 11 (>=11 And <=15)] 
   OR [lower than or equal to 25 
      AND higher than or equal to 21 (>=21 And <=25)] 
   OR [lower than or equal to 35 
      AND higher than or equal to 31 (>=31 And <=35)]
   OR [lower than or equal to 95 
      AND higher than or equal to 91 (>=91 And <=95)]} 

So you might have Bins as follows and only be interested in the the ones in bold-face:
5, 10, 15, 20, 25, 30, 35, 90, 95