424,066 Members | 2,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,066 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??

Thanks in advance
Attached Files
File Type: xls COUNT.xls (279.0 KB, 405 views)
Dec 29 '12 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,286
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
  1. Sub COUNT()
  2. Dim ARRDATA As Variant '§ with range=>array must ALLWAYS be variant.
  3. Dim ROWidx As Integer
  4. Dim COLidx As Integer
  5. '§ COLidxlect amount
  6. Dim ARRCOUNTER() As Integer '§ count results for each line
  7. Dim COUNTER As Integer
  8. Dim TOTAL As Integer
  9. '§ COLidxlect numbers
  10. Dim ARRNUMS() As Integer
  11. Dim ARRNUMSALL() As Integer
  12. '§ sorting
  13. Dim ARRNUMSidx As Integer
  14. Dim ARRNUMSidxMax As Integer
  15. Dim VARSWAP As Integer
  16. Dim SWAPPED As Boolean
  17.  
  18. '§ put sheet1 in array
  19.     Sheets(1).Select
  20. '§                              ( |---------ROWs--------------|,|--------COLs----------------------| )
  21.     ARRDATA = Range("A1").Resize(Range("A1").End(xlDown).ROW, Range("A1").End(xlToRight).Column)
  22. '§ count each ROWidx
  23.     ReDim ARRCOUNTER(1 To UBound(ARRDATA, 1), 1 To 1)
  24.     ReDim ARRNUMSALL(1 To UBound(ARRDATA, 1), 1 To UBound(ARRDATA, 2))
  25.     For ROWidx = 1 To UBound(ARRDATA, 1)
  26.         COUNTER = 0
  27.         ReDim ARRNUMS(0)
  28.         For COLidx = 1 To UBound(ARRDATA, 2)
  29.             If ARRDATA(ROWidx, COLidx) <= 45 Then
  30.                 COUNTER = COUNTER + 1
  31.                 ReDim Preserve ARRNUMS(UBound(ARRNUMS) + 1)
  32.                 ARRNUMS(UBound(ARRNUMS)) = ARRDATA(ROWidx, COLidx)
  33.             End If
  34.         Next
  35.         '§ put COUNTER in ARRCOUNTER
  36.         ARRCOUNTER(ROWidx, 1) = COUNTER
  37.         '§ sort ARRNUMS
  38. '    ARRNUMSidxMin = LBound(ARRNUMS)
  39.         ARRNUMSidxMax = UBound(ARRNUMS) - 1
  40.         Do
  41.             SWAPPED = False
  42.             For ARRNUMSidx = 1 To ARRNUMSidxMax
  43.                 If ARRNUMS(ARRNUMSidx) > ARRNUMS(ARRNUMSidx + 1) Then
  44.                     VARSWAP = ARRNUMS(ARRNUMSidx)
  45.                     ARRNUMS(ARRNUMSidx) = ARRNUMS(ARRNUMSidx + 1)
  46.                     ARRNUMS(ARRNUMSidx + 1) = VARSWAP
  47.                     SWAPPED = True
  48.                 End If
  49.             Next
  50.             ARRNUMSidxMax = ARRNUMSidxMax - 1
  51.         Loop Until Not SWAPPED
  52.         '§ put numbers in ARRNUMSALL
  53.         For ARRNUMSidx = 1 To UBound(ARRNUMS)
  54.             ARRNUMSALL(ROWidx, ARRNUMSidx) = ARRNUMS(ARRNUMSidx)
  55.         Next
  56.     Next
  57. '§ copy ARRCOUNTER to sheet
  58.     Range("Q1").Resize(UBound(ARRCOUNTER), 1) = ARRCOUNTER
  59. '§ calculate TOTAL
  60.     TOTAL = 0
  61.     For ROWidx = 1 To UBound(ARRCOUNTER, 1)
  62.         TOTAL = TOTAL + ARRCOUNTER(ROWidx, 1)
  63.     Next
  64. '§ put TOTAL in sheet
  65.     Range("Q" & CStr((UBound(ARRCOUNTER, 1) + 2))).Value = TOTAL
  66. '§ put ARRNUMS in sheet
  67.     Range("S1").Resize(UBound(ARRNUMSALL, 1), UBound(ARRNUMSALL, 2)) = ARRNUMSALL
  68. '§ clear memory
  69.     ReDim ARRDATA(0)
  70.     ReDim ARRCOUNTER(0)
  71.     ReDim ARRNUMS(0)
  72.     ReDim ARRNUMSALL(0)
  73. End Sub
  74.  
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
  1. [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
  1. {[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

NeoPa
Expert Mod 15k+
P: 31,170
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
  1. =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
  1. =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
  1. =AND(VALUE(RIGHT(A1,1))>0,VALUE(RIGHT(A1,1))<6)
This handles all values less than 100.
Dec 31 '12 #4

zmbd
Expert Mod 5K+
P: 5,285
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:

Expand|Select|Wrap|Line Numbers
  1. {[lower than or equal to 5 (<=5)] 
  2.    OR [lower than or equal to 15 
  3.       AND higher than or equal to 11 (>=11 And <=15)] 
  4.    OR [lower than or equal to 25 
  5.       AND higher than or equal to 21 (>=21 And <=25)] 
  6.    OR [lower than or equal to 35 
  7.       AND higher than or equal to 31 (>=31 And <=35)]
  8.    OR [lower than or equal to 95 
  9.       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

Post your reply

Sign in to post your reply or Sign up for a free account.