CODEWITHSUNDEEP
excelfiltercountformula
Just Me
Just Me

Reputation: 1053

Excel: Formula to Count cells from a column after formatting and filter

I have an Excel with 2 columns, each column has a number of 665 Cells. After formatting and find the Duplicate Values, I made a filter so to show me only the duplicate values.

This is a print screen.

https://snag.gy/K4c3B5.jpg

In this case I want to find the formula that Count cells from column A or B (after formatting and filter) like it shows in the print screen.

As you can see, I try =COUNTA(A1:A5000) into a cell from H Column, but doesn't work. Can anyone help me?

Upvotes: 2

Views: 3421

Answers (3)

jainashish
jainashish

Reputation: 5183

If one need to COUNT the number of visible items in a filtered list, then use the SUBTOTAL function, which automatically ignores rows that are hidden by a filter.

The SUBTOTAL function can perform calculations like COUNT, SUM, MAX, MIN, AVERAGE, PRODUCT and many more (See the table below). It automatically ignores items that are not visible in a filtered list or table. This makes it ideal for showing how many items are visible in a list, the subtotal of visible rows, etc. It also provide control rows hided manually manually.

The solution to your question would be to count the number of non-blank rows visible in Column A and Column B when a filter is active, use:

=SUBTOTAL(103,$A:$A)

Excel Subtotal Formula Arguments

Points to remember when you apply SUBTOTAL formula:

  • When function_num (First argument) is between 1-11, SUBTOTAL includes values that are hidden manually but ignore hidden by filter.
  • When function_num is between 101-111, SUBTOTAL excludes all kind of hidden values.
  • In filtered lists, SUBTOTAL always ignores values in hidden rows, regardless of function_num.
  • SUBTOTAL ignores other subtotals that exist in references are ignored to prevent double-counting
  • SUBTOTAL only work with vertical data values arranged vertically.
  • In Horizontal Hidden Columns, values are always included and never ignored.

Upvotes: 2

Rajesh Sinha
Rajesh Sinha

Reputation: 197

For Filtered data or you can say Visible cell Sutotal & Countifs can be used, coz both ignores Filtered rows.

=Subtotal (3,A1:D10) =Subtotal (103,A1:D10)

To use Countifs a helper col is required, suppose your Filtered data are in Range A2 to D20, then in E2 write this, =if(B2:B20,"UK", 1,"") Then =COUNTIF (B2 :B20, "UK", E2 :E20, "1")

NB: Change cell address as you need.

Upvotes: -1

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9966

Use SubTotal function, which will give you the count of filtered cells only. Also exclude the header from the range, I guess that is not required.

=SUBTOTAL(3,A2:A5000)

Upvotes: 1

Related Questions