# HoW TO USE COUNT FUNCTION FOR A FILTERED CRITERIA

#### ramakrishnan_1731

##### Board Regular
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

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### marka87uk

##### Board Regular
You can use the COUNTIF formula:

=COUNTIF(A:A, CRITERIA)

The criteria for instance could be ">10".

#### Caliche

##### Active Member
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

##### Board Regular
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

##### Well-known Member

Paste this macro into worksheet module:

Code:
``````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
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
Sheets("Results").Delete
On Error GoTo 0
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
ABCD
1WordsCount
2India3
3China2
4england2
5Holland1
6USA1
7France1
8
Results

Does this help?

#### ramakrishnan_1731

##### Board Regular
it wont work

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

#### ramakrishnan_1731

##### Board Regular
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 ?

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

##### Well-known Member
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.

##### Well-known Member
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

Replies
7
Views
299
Replies
3
Views
341
Replies
13
Views
636
Replies
2
Views
384
Replies
7
Views
515

1,181,649
Messages
5,931,216
Members
436,784
Latest member
amuljono

### 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.

### Which adblocker are you using?

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

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