Couting with conditions

GuillaumeJ

New Member
Joined
Mar 10, 2004
Messages
27
hi everyone,

I want to create a short macro to count the number of cells in a column, based on a condition.
All the cells contains either "yes" or "no", and the options of counting are the number of yes, the number of no and all of them

Let's say I have all my values in the range A1:A100, my condition is registered in cell B1, knowing that the value can be "Yes", "No", "All"

My code is the following:

For i = 1 to 100
If range("A" &i).Value = Range("B1").value Then
count = count +1
Else
count = count
End if
Next i

Is there a way to put in the Range("B1").Value a kind of wildcard when the condition "All" is selected so then only my condition in B1 will change and not the macro itself ?

It may look a bit complex, but I want to count items with various conditions located in several cells, so I will keep this "If" adding conditions on anothers cells.

Any help will be great !

guillaume
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
You don't necessarily need to use a macro:
Book1
ABCD
1ListConditionTotal
2YesAll15
3No
4Yes
5No
6Yes
7No
8Yes
9Yes
10No
11Yes
12No
13Yes
14No
15No
16No
Sheet1
 
Upvote 0
If you are dead-set on doing it with a macro, you can use the same worksheet functions as I posted above to count without looping through the cells:

Code:
Sub CountItems()
Dim rngCount As Range, rngCondition As Range
Dim rngTotal As Range, myCount As Integer

Set rngCount = Range("A1:A100")
Set rngCondition = Range("B2")
Set rngTotal = Range("C2")

Select Case rngCondition.Value
    Case Is = "Yes", "No"
        myCount = WorksheetFunction.CountIf(rngCount, rngCondition.Value)
    Case Is = "All"
        myCount = WorksheetFunction.CountA(rngCount)
End Select

rngTotal.Value = myCount

End Sub
 
Upvote 0
thanks but I thought already on using the case select option but I think it won't fit because I have to look through at least six others cells.

Basically, I have a database containing contracts and details on it, if they have some options or not. I want to count the contracts meeting conditions. Those conditions are selected with radio buttons, hence the yes, no and all.

What I wanted to do is a for loop, checking in each cell of the "contract row" if the values are meeting the conditions I put. And since in some conditions I want to consider all the values possibles, I was wondering if it was possible somehow to put a kind of wildcard when I check the value.

That's why I think the case scheme will far too complex to manage.
 
Upvote 0
I tried to use the excel add in to put my sheet in html but it's not working..

I put it anyway...

Contract Nb. Dealer Brand Type Condition
101 Dealer 1 Brand 1 Type 1 Yes
102 Dealer 1 Brand 1 Type 2 Yes
103 Dealer 1 Brand 2 Type 2 No
104 Dealer 2 Brand 2 Type 1 No
105 Dealer 1 Brand 2 Type 2 No
106 Dealer 2 Brand 2 Type 1 Yes
107 Dealer 2 Brand 1 Type 2 No
108 Dealer 3 Brand 2 Type 1 Yes


As you can see, there are 5 fields, giving the details on the contracts numbers. I have a main page where I can specify the conditions for the counting, dealer 1, dealer 2 or both of them, and the same for all the others fields.
The conditions are saved and then I would like to have a macro giving me the counts of contracts following the given conditions, to create in fact a autofilter function in Excel.
Is there a way to do it ?

Thank you in advance !
 
Upvote 0
You are selecting all of the conditions with radio buttons--are they from the forms menu or the control toolbox? I'm assuming they are on the sheet and not on a userform, correct?
 
Upvote 0
Just playing around a bit until you answer, but this works for me using validation dropdown lists to select the conditions and a formula to get the total:
Book1
ABCDEFGHI
1Contract Nb.DealerBrandTypeConditionDealerCondition# of contracts
2101Dealer 1Brand 1Type 1YesDealer 1No2
3102Dealer 1Brand 1Type 2Yes
4103Dealer 1Brand 2Type 2No
5104Dealer 2Brand 2Type 1No
6105Dealer 1Brand 2Type 2No
7106Dealer 2Brand 2Type 1Yes
8107Dealer 2Brand 1Type 2No
9108Dealer 3Brand 2Type 1Yes
Sheet1
 
Upvote 0
Sorry for the delay,

Yes, my radio buttons are in the sheet, not in a form.

I finally found a solution to my problem, by using autofilters in my macros, with the criterias coming from the radiobuttons.

However, I now have a new problem, when I want to count the number of rows resulting from the autofilter.
Assuming that datasheet is the name of my sheet, how can I get the range of visible cells in this worksheet after applying the autofilter ?
I tried to use the SpecialCells(xlCellTypeVisible) but it seems it is not working.

So let's say I have 3 remaining rows after my autofilter, how can I get this in VBA ? I tried the following:
WorksheetFunction.CountA(Datasheet.UsedRange.SpecialCells(xlCellTypeVisible).Column(1))

Thank you very much for your help so far anyway !
 
Upvote 0
You have to tell it what range to count the visible cells in.

So something like

Range("A2:A100").SpecialCells(xlCellTypeVisible).Rows.Count

should return the number of rows currently visible within the range A2:A100
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top