435,454 Members | 3,191 Online
Need help? Post your question and get tips & solutions from a community of 435,454 IT Pros & Developers. It's quick & easy.

Count multiple criteria vba excel

P: 7
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??

Attached Files
 COUNT.xls (279.0 KB, 436 views)
Dec 29 '12 #1
4 Replies

 Expert Mod 10K+ P: 12,366 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. Dec 30 '12 #2

 P: 7 the code that is working for only one mathematical condition is, for example numbers are lower than 45 : Expand|Select|Wrap|Line Numbers 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 : Expand|Select|Wrap|Line Numbers [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: Expand|Select|Wrap|Line Numbers {[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 Dec 30 '12 #3

 Expert Mod 15k+ P: 31,494 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 : Expand|Select|Wrap|Line Numbers =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 : Expand|Select|Wrap|Line Numbers =COUNTIF(X1:X?,TRUE) where X is the column letter and ? refers to the bottom row containing data. PS. A shorter formula might be : Expand|Select|Wrap|Line Numbers =AND(VALUE(RIGHT(A1,1))>0,VALUE(RIGHT(A1,1))<6) This handles all values less than 100. Dec 31 '12 #4

 Expert Mod 5K+ P: 5,397 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=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 Dec 31 '12 #5