HoW TO USE COUNT FUNCTION FOR A FILTERED CRITERIA

ramakrishnan_1731

Board Regular
Joined
Jun 11, 2007
Messages
73
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
Joined
Mar 24, 2007
Messages
247
You can use the COUNTIF formula:

=COUNTIF(A:A, CRITERIA)

The criteria for instance could be ">10". :)
 

Caliche

Active Member
Joined
Mar 26, 2002
Messages
339
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
Joined
Jun 11, 2007
Messages
73
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

Board Regular
Joined
Mar 24, 2007
Messages
247
Sorry I misunderstood your question. :)
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
Hi, how about this as a solution:

If A1:A76 as your range

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
                .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
ABCD
1WordsCount
2India3
3China2
4england2
5Holland1
6USA1
7France1
8
Results


Does this help?
 

ramakrishnan_1731

Board Regular
Joined
Jun 11, 2007
Messages
73
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 ?
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
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
 

Forum statistics

Threads
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.
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
Top