473,698 Members | 2,084 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Count multiple criteria vba excel

7 New Member
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, 526 views)
Dec 29 '12 #1
4 5282
Rabbit
12,516 Recognized Expert Moderator MVP
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
DANNYOCEAN
7 New Member
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
32,569 Recognized Expert Moderator MVP
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
5,501 Recognized Expert Moderator Expert
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–Shneiderm an 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

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

Similar topics

4
21096
by: ShyGuy | last post by:
I have a table with 4 fields. Three are used for criteria. I can get the DLookup to work with 1 criteria with the following but can't get it to work with 2 or three. NumofAppts = DLookup("", "LookUpAppts", " = " & Forms!!NumofPeople) Can someone tell me how to add multiple criteria? I tried "And" but it doesn't seem to work.
3
3919
by: developing | last post by:
Hello How do I specify multiple criteria for FindFirst or FindRecord (or anything else) that takes the criteria from a form. (text field and number field) edit: this will be in the after update event for a list box. my intent is that user can select a value from the list box based on which other list box will be filled. the search criteria will take in value from the first list box and customer number from another control on the main...
4
3331
by: JHNielson | last post by:
I have a query that I'm trying to update with a dlookup with multiple criteria This is the string: EVNT_DT: DLookUp("","","( .EVNT_QTR=.) & (.=.)") When i run it it says it can't find the name PRC_15011 - Find_Vendor_Mgmt_Fees.EVNT_QTR. But I know the names are right, I've checked them three times.
1
5679
by: 2D Rick | last post by:
I want to open a report using OpenReport and passing it a Where Clause with multiple criteria. I know the Where Clause below is way off but it conveys what I need. Dates are of string type. DoCmd.OpenReport "rpt_LOG", acViewPreview, , WHERE???? strDate1 = "Friday, July 20, 2007" strDate2 = "Saturday, July 21, 2007"
0
2027
by: ChadK | last post by:
I am trying to open a report based on what the user selects on a form. Each individual criteria works but when I try to combine to pass multiple criteria it doesn't. I have read what I can find on here and tried different suggestions but nothing works. Can somebody take a look at my code and see what I'm doing wrong? strDocName = "ColdCallTotalsbyBDM" strLinkCriteria1 = " = Forms!!" strLinkCriteria2 = " >= Forms!!" strLinkCriteria3 = "...
2
3481
by: Emre DÝNÇER | last post by:
is it possible to have a multiple criteria switch in C# switch(name , surname){ case "John","Smith" break; } thanks in advance
1
5135
by: akirekab | last post by:
I am using DCount, but I am not able to find how to set simple multiple criteria. Here is sample of what i need. =DCount("PatientProfileID","qryFaceToFaceReason_EAP_VG","FaceToFaceReasonID=2" Or "FaceToFaceReasonID=0") Any help in syntax or formatting would be appreciated. Note if I just enter ther first criteria it works fine. I was hoping the OR could be used one way or another.
3
3634
by: kstevens | last post by:
Please help. I know the sysntax is wrong. Here are some details. I am looking for the sum of past shipped items. I originally wrote this Dsum with only one criteria, but because of multiple lineitems with the same number it was adding all of the items with the same ....obviously. So i tried to just find records with multiple criteria..... part number (formatted as text) and the (formatted as number). Nz(DSum("shipqty",...
1
4113
by: Brendan Wolf | last post by:
Happy Halloween all, I have been struggling at work with a DLookup using multiple criteria. I would like a text box to display the results of a DLookup based on the values selected in three comboboxes and the table that runs them all. Here is my code: Private Sub Subverbs_GotFocus() 'When the Subverbs gets focus it looks up proper subverbs from Authority_Lookup table Me.Subverbs = DLookup("", "AUTHORITY_LOOKUP", "= '" &...
3
3197
by: Brendan Wolf | last post by:
Happy Halloween all, I have been struggling at work with a DLookup using multiple criteria. I would like a text box to display the results of a DLookup based on the values selected in three comboboxes and the table that runs them all. Here is my code: Private Sub Subverbs_GotFocus() 'When the Subverbs gets focus it looks up proper subverbs from Authority_Lookup table Me.Subverbs = DLookup("", "AUTHORITY_LOOKUP", "= '" &...
0
8672
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8600
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9021
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8860
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7712
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5860
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4361
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2323
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1998
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.