COUNT.IF conditions/CRITERIA

[TONYSTARK]

hello guys!
how are you?
In excel, how can we use COUNT.IF function with multiple conditions/CRITERIA?

for example:
11 26 47 55 57 59 62 68 71 76 78 81 83 94 99

I wanna calculate with COUNT.IF function how many numbers fill up the following ALTERNATIVE CRITERIA:

COUNT.IF OF [ (A1:A15); "=11" OR ">=55 AND <=71" OR ">=83")]

TRANSLATING: how many numbers in the list above, are equal to 11 OR are greaten than/equal to 55 and lower than/equal to 71 OR are greaten than/equal to 83.?

Writen results are: 11, 55, 57, 59, 62 , 68, 71, 83, 94, 99

Thus COUNT.IF function should display the answer = 10 numbers.

It'd be useful to have a COUNT.IF function that do all the calculation with multiple conditions/CRITERIA.


I was wondering that´s possible do it in excel/vba but I don't know how to start.

Can someone give a hand??

I'm looking forward to receiving good news.

[Guido Geurs]

This macro will do it.

Sub Macro1()
Dim COUNTER As Integer
    Range("A1").Activate
    Do While ActiveCell.Value <> ""
        With ActiveCell
            If .Value = 11 Or _
                (.Value >= 55 And .Value <= 71) Or _
                .Value >= 83 Then _
                    COUNTER = COUNTER + 1
            .Offset(0, 1).Activate
        End With
    Loop
    Range("A2").Value = COUNTER
End Sub

[TONYSTARK]

I´m so gratefull for your aid.
It really works.
I´d like to do this, to make this all calculation for each line for a extremely large number list in a spreadsheet like excel.
for example:

line 01: 90 95 30 40 98 65 67 10 20 51
line 02: 55 68 35 69 75 77 79 80 84 85
and so on... for line 03 , line 04, line 5000, ... .


The spreadsheet should display the total result, as you did above, and the explicit writen results for each line in the number list.
As was done in the previous post:

Writen results are: 11, 55, 57, 59, 62 , 68, 71, 83, 94, 99

Thus COUNT.IF function should display the answer = 10 numbers.

Can you give some advice??

Thanks in advance

[MikeTheBike]

Hi

You can create your own user define function, Say ValuesCount()

Which could look something like this

Function ValuesCount(ByRef ThisRange As Range) As Integer
    Dim cel As Range
 
    For Each cel In ThisRange
        If IsNumeric(cel) Then
            Select Case True
                Case cel.Value = 11
                    ValuesCount = ValuesCount + 1
                Case cel.Value >= 83
                    ValuesCount = ValuesCount + 1
                Case (cel.Value >= 55 And cel.Value <= 75)
                    ValuesCount = ValuesCount + 1
            End Select
        End If
    Next cel
End Function

This should be pasted in a Code Module (not the Sheet or ThisWorkbook Module).

And used in the requied cell like this
=ValuesCount(A1:O1)
for example.

You can use this anywhere in the work book and specify any range you like.

HTH


MTB

[Guido Geurs]

If you have a sheet with more then 5000 lines, it's better to put the sheet in an array (much, much faster).
The code of the macro (not a function) will be:

Sub COUNT()
Dim ARRAYDATA As Variant '§ with range=>array must ALLWAYS be variant.
Dim ARRAYCOUNTER() As Integer '§ count results for each line
Dim ROW As Integer
Dim COL As Integer
Dim COUNTER As Integer
Dim TOTAL As Integer
'§ put sheet1 in array
    Sheets(1).Select
'§                                ( |------rows--------------|,|-----cols----------------------| )
    ARRAYDATA = Range("A1").Resize(Range("A1").End(xlDown).ROW, Range("A1").End(xlToRight).Column)
'§ count each row
    ReDim ARRAYCOUNTER(1 To UBound(ARRAYDATA, 1), 1 To 1)
    For ROW = 1 To UBound(ARRAYDATA, 1)
        COUNTER = 0
        For COL = 1 To UBound(ARRAYDATA, 2)
            If ARRAYDATA(ROW, COL) = 11 Or _
                (ARRAYDATA(ROW, COL) >= 55 And ARRAYDATA(ROW, COL) <= 71) Or _
                ARRAYDATA(ROW, COL) >= 83 Then _
                    COUNTER = COUNTER + 1
        Next
        ARRAYCOUNTER(ROW, 1) = COUNTER
    Next
'§ copy ARRAYCOUNTER to sheet
    Range("L1").Resize(UBound(ARRAYCOUNTER), 1) = ARRAYCOUNTER
'§ calculate TOTAL
    TOTAL = 0
    For ROW = 1 To UBound(ARRAYCOUNTER, 1)
        TOTAL = TOTAL + ARRAYCOUNTER(ROW, 1)
    Next
'§ put TOTAL in sheet
    Range("L" & CStr((UBound(ARRAYCOUNTER, 1) + 2))).Value = TOTAL
End Sub

[TONYSTARK]

@Guido Geurs
Many thanks for your help.
It really works.
The spreadsheet should display the total result for each line, as you did above, but it didn´t display the explicit writen results for each line in the number list.
for example:
11 26 47 55 57 59 62 68 71 76 78 81 83 94 99



Thus COUNT.IF function should display the writen results at the right side of the spreadsheet : 11, 55, 57, 59, 62 , 68, 71, 83, 94, 99

Shall you fix it up?

Thanks in advance

[Guido Geurs]

this will add the explicit numbers to the sheet: see attachment