HoW TO USE COUNT FUNCTION FOR A FILTERED CRITERIA

[ramakrishnan_1731]

Dear Experts,

In an excel sheet A1 to ...... colum i have some cirteria. If I filter a criteria i should get the count for it. Kinldy let me know with out using VB how is it possible.

Regards
R.Ramakrishnan

 

[marka87uk]

You can use the COUNTIF formula:

=COUNTIF(A:A, CRITERIA)

The criteria for instance could be ">10".

 

[Caliche]

Dear Experts,

In an excel sheet A1 to ...... colum i have some cirteria. If I filter a criteria i should get the count for it. Kinldy let me know with out using VB how is it possible.

Regards
R.Ramakrishnan

Use

Subtotal(3,A3:A65536)

in A1 (supposing data is in A3:A65536), and column heading is A2

HTH

Caliche

 

[ramakrishnan_1731]

HI

You can use the COUNTIF formula:

=COUNTIF(A:A, CRITERIA)

The criteria for instance could be ">10".

I am unable to understand this formula.

I can explain again

IN colum a1:a76 different countries some countries coming repeated also

if i filter a country i should get the count of that country in one colum

pls help

 

[marka87uk]

Sorry I misunderstood your question.

 

[ADAMC]

Hi, how about this as a solution:

If A1:A76 as your range

Paste this macro into worksheet module:

Sub count()
Dim a, v, s, z, i  As Long, w(), Rng As Range
a = Range("a1").CurrentRegion
With CreateObject("scripting.dictionary")
    .comparemode = vbTextCompare
    For Each v In a
        s = Split(v, " ")
        For i = 0 To UBound(s)
            If Not .exists(s(i)) Then
                ReDim w(1 To 2): w(1) = s(i): w(2) = 1
                .Add s(i), w
            Else
                w = .Item(s(i)): w(1) = s(i)
                w(2) = .Item(s(i))(2) + 1
                .Item(s(i)) = w
            End If
        Next
    Next: z = .items
End With
On Error Resume Next
Application.DisplayAlerts = 0
Sheets("Results").Delete
On Error GoTo 0
Sheets.Add.Name = "Results"
With Sheets("Results").[a1]
    .Resize(, 2).Value = [{"Words","Count"}]
    For i = 0 To UBound(z)
        .Offset(i + 1).Resize(, UBound(z(i))).Value = z(i)
    Next
    .Resize(UBound(z) + 2, 2).Sort .Cells(1, 2), xlDescending
End With
End Sub

Will create a new sheet and give all words an the count:

Book1
	A	B	C	D
1	Words	Count
2	India	3
3	China	2
4	england	2
5	Holland	1
6	USA	1
7	France	1
8
Results

Does this help?

 

[ramakrishnan_1731]

it wont work

Sorry I misunderstood your question.

sub total will work for a numeric only if the criteria is a not a numeric then what i shoul do ?

 

[ramakrishnan_1731]

Re: it wont work

Sorry I misunderstood your question.

sub total will work for a numeric only if the criteria is a not a numeric then what i shoul do ?

i dont want to use vb . is there any other way with out using vb

 

[ADAMC]

Either that or just use a subtotal formula:


=SUBTOTAL(3,A:A) place this in C1 (Or anywhere)

If column A has a header row use =SUBTOTAL(3,A:A)-1

Now whichever you filter will give you the count.

 

[ADAMC]

Re: it wont work

sub total will work for a numeric only if the criteria is a not a numeric then what i shoul do ?

=SUBTOTAL(3,A:A) the 3 in this formula is the function number to search for text and works fine for me.
Function number 2 searches numeric data......formula above should be fine.

Another way...................filter your country.....highlight the whole column
Move the mouse down to the space below excel just above the task bar..right click and choose count.
in the bottom right you will see a field which says count =

Hope this helps