## Excel Count filtered row using SUBTOTAL function

In this article, we will learn how to Count visible filtered rows.

**Scenario:**

In simple words, while working with table values, sometimes we need to count, sum, product or average of the values having already applied filter option. Example if we need to get the count of a certain IDs where the ID data has applied filter on. Criteria on data is applied using the filter option.

**How to solve the problem ?**

For this article we will be required to use the SUBTOTAL function. Now we will make a formula out of the function. Here we are given some values in a range and specific text value as criteria. We need to count the values where the formula includes all the values which ends with the given text or pattern

**Generic formula:-**

= SUBTOTAL ( fun_num, range )

fun_num : number, num corresponding to the operation

range : range where operation is to apply.

**Example:**

All of these might be confusing to understand. So, let's test this formula via running it on the example shown below.

Here we have the order import data. And we have applied criteria to Region, only the East region is filtered.

Here to find the count of filtered values. Choose the right argument as fun_num. Fun_num is the operation you want to apply. Here to count the cells we use COUNTA operation, num as 3.

Use the formula:

= SUBTOTAL ( 3, B2:B14)

As you can see the total rows which are visible comes out to be 11. Now we will use one more example to extract the Average of the quantity which are visible or filtered.

Use the formula:

= SUBTOTAL ( 1 , C2:C14 )

As you can see the average order quantity received from the East region comes out to be approx 60. You can use different operations like average, count, max, min, product, standard deviation, sum or variation as per the required results on filtered data.

Here are some observational notes shown below.

Notes:

- Choose the right fun_num to get the desired results.
- The formula only works with numbers and text both
- Operators like equals to (
**=**), less than equal to (**<=**), greater than (**>**) or not equals to (**<>**) can be performed within function applied on numbers only.

Hope this article about how to Count visible filtered rows in Excel is explanatory. Find more articles on COUNTIFS functions here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at [email protected]

**Related Articles**

**How to use the SUBTOTAL function in Excel**: Returns the SUM, COUNT, AVERAGE, STDEV or PRODUCT on applied filtered data in Excel.

**COUNTIFS with Dynamic Criteria Range** : Count cells dependent on other cell values in Excel.

**COUNTIFS Two Criteria Match** : Count cells matching two different criteria on list in excel.

**COUNTIFS With OR For Multiple Criteria** : Count cells having multiple criteria match using the OR function.

**The COUNTIFS Function in Excel** : Count cells dependent on other cell values.

**How to Use Countif in VBA in Microsoft Excel** : Count cells using Visual Basic for Applications code.

**How to use wildcards in excel** : Count cells matching phrases using the wildcards in excel

**Popular Articles**

**50 Excel Shortcut to Increase Your Productivity**

**Edit a dropdown list**

**Absolute reference in Excel**

**If with conditional formatting**

**If with wildcards**

**Vlookup by date**

Permalink

I don't understand the formula, but it works great for counting filtered data!!! I changed the data "A" in the example to a cell containing a value (string) I wanted to count. Setting the data range to match mine was easy.=SUMPRODUCT(SUBTOTAL(3,OFFSET(T$5:T$885,ROW(T$5:T$885)-ROW(T$5),0,1)),--(T$5:T$885=S889))

*Post by DKS*

Hi,

I would like to use the COUNTIF (or SUMIF) only for filtered rows. Thus

somewhere a SUBTOTAL kind of functionality but for COUNTIF.

Is it possible to simulate (maybe with array formulas)?

Many thanks in anticipation.

*Post by T. Valko*

A1:B1 = column headers

A2:B11 = data

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),--(B2:B11="A"))

--

Biff

Microsoft Excel MVP

Headers K26:T26

Data G26:T818

Thanks

Submitted via EggHeadCafe

ASP.NET- How to Raise Custom Events from a UserControl

http://www.eggheadcafe.com/tutorials/aspnet/d5f6cb27-dc1b-4233-86c0-aa8437138e47/aspnet-how-to-raise-custom-events-from-a-usercontrol.aspx

## Count visible rows only with criteria

At the core, this formula works by setting up two arrays inside SUMPRODUCT. The first array applies criteria, and the second array handles visibility:

The criteria is applied with part of the formula:

Which generates an array like this:

Where TRUE means "meets criteria". Note because we are using multiplication (*) on this array, the TRUE FALSE values will automatically be converted to 1's and 0's by the math operation, so we end up with:

The visibility filter is applied using SUBTOTAL, with function number 103.

SUBTOTAL is able to exclude hidden rows when running calculations, so we can use it in this case to generate a "filter" to exclude hidden rows inside of SUMPRODUCT. The problem though is that SUBTOTAL returns a single number, while we need an array of results to use it successfully inside SUMPRODUCT. The trick is to use OFFSET to feed SUBTOTAL one reference per row, so that OFFSET will return one result per row.

Of course, that requires another trick, which is to give OFFSET an array that contains one number per row, starting with zero. We do that with an expression built on the ROW function:

which will generate an array like this:

In summary, the second array (which handles visibility using SUBTOTAL), is generated like this:

And, finally, we have:

Which returns 1.

### Multiple criteria

You can extend the formula to handle multiple criteria like this:

### Summing results

To return a sum of values instead of a count, you can adapt the formula to include a sum range:

The criteria and visibility arrays work the same as explained above, excluding cells that are not visible. If you need partial matching, you can construct an expression using ISNUMBER + SEARCH, as explained here.

### How to Countif filtered data/list with criteria in Excel?

You may notice that no matter you have filtered your table or not, the COUNTIF function will ignore the filtering and return a fixed value. In some cases, you are required to count filtered data with a specific criteria, so how to get it done? In this article, I will introduce a couple of ways to Countif filtered data/list in Excel quickly.

#### Countif filtered data with criteria by adding a help column in Excel

In this article, I will take the following table as example. Here, I have filtered out Julie and Nicole in the Salesman column.

Original data:

Filtered data:

This method will guide you to add an extra help column, and then you can apply the COUNTIFS function to count the filtered data in Excel. (*Note*: This method requires you have filtered your original table before following steps.)

**1**. Find a blank cell besides the original filtered table, says Cell G2, enter =IF(B2="Pear",1,""), and then drag the Fill Handle to the range you need. (*Note:* In the formula of =IF(B2="Pear",1,""), B2 is the cell you will count, and the "Pear" is the criteria you will count by.)

Now a help column is added besides original filtered table. The "1" indicates it's pear in the Column B, while blank hints it's not pear in Column B.

**2**. Find a blank cell and enter the formula =COUNTIFS(B2:B18,"Pear",G2:G18,"1"), and press the **Enter** key. (*Note: *In the formula of =COUNTIFS(B2:B18,"Pear",G2:G18,"1"), the B2:B18 and G2:G18 are ranges you will count, and "Pear" and "1" are criteria you will count by.)

Now you will get the count number at once. Please note that the count number will not change if you disable filtering or change filtering.

#### Sum/Count/Average visible cells only in a specified range with ignoring hidden or filtered cells/rows/columns

The normally SUM/Count/Average function will count all cells in the specified range on matter cells are hidden/filtered or not. While the Subtotal function can only sum/count/average with ignoring hidden rows. However, Kutools for Excel **SUMVISIBLE**/**COUNTVISIBLE**/**AVERAGEVISIBLE** functions will easily calculate the specified range with ignoring any hidden cells, rows, or columns. **Full Feature Free Trial 30-day!**

#### Countif filtered data with criteria by Excel functions

If you want the count number changes as the filter changes, you can apply the SUMPRODUCT functions in Excel as following:

In a blank cell enter the formula =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1)),ISNUMBER(SEARCH("Pear",B2:B18))+0), and press the **Enter** key.

Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future! Read more…Free trial |

**Notes:**

(1) In the above formula, B2:B18 is the range you will count, and "Pear" is the criteria you will count by.

(2) The returning value will change when you disable filtering or filtering changes.

#### Easily split a range to multiple sheets based on criteria in a column in Excel

Comparing to complex array formulas, it may be much easier to save all filtered records into a new worksheet, and then apply the Count function to count the filtered data range or list.

Kutools for Excel’s**Split Data** utility can help Excel users easily split a range to multiple worksheets based on criteria in one column of original range. **Full Feature Free Trial 30-day!**

#### Related Articles

### The Best Office Productivity Tools

#### Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

**Reuse**: Quickly insert**complex formulas, charts**and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

**Read More...Free Download...Purchase... **

#### Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

**Enable tabbed editing and reading in Word, Excel, PowerPoint**, Publisher, Access, Visio and Project.- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

## Countif excel subtotal

You may use below formula which will NOT count hidden values:

=SUMPRODUCT(--($D$2:$D$202="MT"),--($C$2:$C$202=B2),SUBTOTAL(103,OFFSET(D2,ROW($D$2:$D$202)-ROW($D$2),0)))

Range $D$2:$D$202 validates "MT"

Range $C$2:$C$202 validates Date per Cell B2

The formula returns the COUNT of entries (ignores hidden values) in the range $D$2:$D$202 which are "MT" & where the Range $C$2:$C$202 corresponds to the date in cell B2. Use "SUBTOTAL(3" in lieu of "SUBTOTAL(103" to count hidden values also.

Regards,

Amit Tandon

#### Report abuse

Harassment is any behavior intended to disturb or upset a person or group of people. Threats include any threat of suicide, violence, or harm to another. Any content of an adult theme or inappropriate to a community web site. Any image, link, or discussion of nudity. Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect. Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software. Unsolicited bulk mail or bulk advertising. Any link to or advocacy of virus, spyware, malware, or phishing sites. Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct. Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation.2 people found this reply helpful

·Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi,

Suppose your data is in range L2:M21 - headings are in L2:M2. Dates are in column L and text entries are in column M. Apply an date filter and type MT in cell A1. Enter this array formula (Ctrl+Shift+Enter)

=SUMPRODUCT(IF(SUBTOTAL(3,OFFSET(L3,ROW(L3:L21)-ROW(L3),))*($M$3:$M$21=A1),1,0))

Hope this helps.

Ashish Mathur

www.ashishmathur.com

http://twitter.com/excelashish

#### Report abuse

Harassment is any behavior intended to disturb or upset a person or group of people. Threats include any threat of suicide, violence, or harm to another. Any content of an adult theme or inappropriate to a community web site. Any image, link, or discussion of nudity. Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect. Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software. Unsolicited bulk mail or bulk advertising. Any link to or advocacy of virus, spyware, malware, or phishing sites. Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct. Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation.Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

## Add Countif to Array Formula (Subtotal) in Excel

I am new to array formulae and have noticed that while includes many functions, it does not feature (only and ).

I'm trying to figure out how I can integrate a -like feature to my array formula.

I have a matrix, a small subset of which looks like:

Thanks to the help of @Tom Shapein this post, I (he) was able to average the sum of each row in the matrix provided it had complete data (so rows 2 and 4 in the example above would not be included).

Now I would like to count the number of rows **with complete data (so rows 2 and 4 would be ignored)** which include at least one value above a given threshold (say 45).

In the current example, the result would be 2, since row 1 has 5/5 values > 45, and row 3 has 1 value > 45. Row 5 has values < 45 and rows 2 and 3 have partially or fully missing data, respectively.

I have recently discovered the function and think that perhaps could be useful but I'm not sure how to integrate it within Tom Sharpe's elegant code, e.g.,

**Remember, I am no longer looking for the average: I want to filter rows for whether they have full data, and if they do, I want to count rows with at least 1 entry > 45.**

### Now discussing:

- Westwood one talk radio stations
- Bed wars server
- Stalingrad movie 2020
- Olx philippines
- Stair edging for tile
- Samsung dynamic wallpaper
- Big thunder boat rental
- Cathedral hill carpet
- Generations christian church
- Xfinity race today

## COUNTIF and COUNTIFS with SUBTOTAL

08-26-2015, 11:30 AM#1

### COUNTIF and COUNTIFS with SUBTOTAL

I have googled and used this forum to figure out how to combine SUBTOTAL with other formulas. For instance, here's one formula I've used to calculate an average that ignores cells with a zero value: =AVERAGE((IF(SUBTOTAL(9,OFFSET(L$7,ROW(L$7:L$112)-ROW(L$7),0))<>0,SUBTOTAL(9,OFFSET(L$7,ROW(L$7:L$112)-ROW(L$7),0)))))

However, I'm not entirely sure what's going on in that formula and I have a more complicated formula that I'd like to reflect the filtered values. Here's the formula: =(COUNTIF(L7:L112, "<0"))/(COUNTIFS(L7:L112,"<>0",P7:P112,"=Yes"))

It works as intended (gives me a percentage that I'm looking for) but how would I change it to work with SUBTOTAL?

08-26-2015, 12:28 PM#2

08-26-2015, 01:25 PM#3

### Re: COUNTIF and COUNTIFS with SUBTOTAL

Thanks for your help. However, I tried your formula and it looks like it doesn't work as intended. It still counts all the values in the range and the output value does not change based on what filter is on (and I checked the data, it should definitely change). Unfortunately I don't know enough about array formulas to fix this although they seem to be extremely useful so I need to find time to start learning that.

08-26-2015, 01:48 PM#4

08-26-2015, 02:25 PM#5

### Re: COUNTIF and COUNTIFS with SUBTOTAL

Thanks very much, that works very well.

The thing is that my sheet is very "wide" (176 columns in use) and the data is formatted in a way that I'd rather not use helper columns if at all possible. Do you or anyone else know how to achieve the same result without using a helper column - is that even possible?

08-26-2015, 02:36 PM#6

08-26-2015, 02:40 PM#7

### Re: COUNTIF and COUNTIFS with SUBTOTAL

Ok, that makes sense. Thanks very much your help, it's much appreciated!

08-26-2015, 02:43 PM#8

### Re: COUNTIF and COUNTIFS with SUBTOTAL

Try this...Originally Posted byJFactor=(COUNTIF(L7:L112, "<0"))/(COUNTIFS(L7:L112,"<>0",P7:P112,"=Yes"))

It works as intended (gives me a percentage that I'm looking for) but how would I change it to work with SUBTOTAL?

=SUMPRODUCT(SUBTOTAL(2,OFFSET(L7,ROW(L7:L112)-ROW(L7),0)),--(L7:L112<0))/SUMPRODUCT(SUBTOTAL(2,OFFSET(L7,ROW(L7:L112)-ROW(L7),0)),--(L7:L112<>0),--(P7:P112="Yes"))

Biff

Microsoft MVP Excel

Keep It Simple Stupid

Let's Go Pens. We Want The Cup.

08-26-2015, 03:08 PM#9

08-26-2015, 04:11 PM#10

### Re: COUNTIF and COUNTIFS with SUBTOTAL

Excellent. Thanks Tony Valko, that formula works beautifully.

08-26-2015, 05:51 PM#11

**505**506 507