Countif subtotal

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:

1. Choose the right fun_num to get the desired results.
2. The formula only works with numbers and text both
3. 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

Sours: https://www.exceltip.com/excel-subtotals/excel-count-filtered-row-using-subtotal-function.html

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.

It sounds like you can simply change COUNTIF to COUNTIFS and add date criteria.

For January, 2020.

=COUNTIFS(Accepted!W7:W1500,"subsidided rental", Accepted!A7:A1500, ">="&DATE(2020, 1, 1), Accepted!A7:A1500, "<"&DATE(2020, 2, 1))

Sorry this didn't help.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

So, on the "Accepted" sheet, I have already a filter that is counting data for the year.  what I need is, if I filter data sheet 1 (Accepted) for say April.  I need data sheet 2 to only count data for April.

I have a sum function that works very well when I filter the months.

=SUBTOTAL(109, Accepted!S5:S1550)

But the SUBTOTAL function does not give me an option for COUNTIF

Hope this helps

Sorry this didn't help.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

There is a complicated method to COUNTIF filtered data involving SUMPRODUCT, INDIRECT, and SUBTOTAL. Yours is further complicated with the subsidided rental criteria. Just use the formula I supplied earlier with column S instead of column A and the DATE adjusted for April.

BTW, subsidided is commonly spelled subsidized.

Sorry this didn't help.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

Thank you, I tried the formula you gave me and it doesn't work.  My problem is, that it's a live document, with data sheet 1 only accessible to certain population with PII information, and sheet 2 has only aggregate data.   If I need to filter data from sheet 1 into sheet 2 for 1, 2 months, or for the first quarter of the year, I need for the data to be maintained for the months I have filtered in sheet 1.

I agree with you that subsidized is spelled that way, but I also have 3 other forms of subsidized rental criteria and this is what "The bosses" decided to keep them separate.

This is what data sheet 2 looks like.   I've labeled it APRIL.

Sorry this didn't help.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

COUNTIF to EXCLUDE cells / rows hidden with Autofilter: use SUBTOTAL with SUMPRODUCT to COUNT column A values where column B = "A".

=SUMPRODUCT((B2:B20="A")*SUBTOTAL(103,OFFSET(A2,ROW(A2:A20)-ROW(A2),)))

Regards,

Amit Tandon

www.excelanytime.com

Sorry this didn't help.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

Hi Amit,

Can the formula work if the filtered data is in another tab (sheet 1)?  and the filtered data is captured in sheet 2?    I have a Sum formula that captures the filter very good, but the COUNTIF does not work the same.

The formula that I have to sum is:

=SUBTOTAL(109, Accepted!S5:S1550)

Will your formula for COUNTIF be described as follows:

=SUMPRODUCT((!Accepted!B2:B20="Subsidized Rent")*SUBTOTAL(103,OFFSET(B2,ROW(B2:B20)-ROW(B2),)))

Sorry this didn't help.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

The formula will work well.

=SUMPRODUCT((Accepted!B2:B20="Subsidized Rent")*SUBTOTAL(103,OFFSET(B2,ROW(B2:B20)-ROW(B2),)))

This will work where column B in the "Accepted" tab = "Subsidized Rent" and corresponding cells of column B in the formula sheet are counted in which some cells / rows are hidden by a filter. Using the above formula will EXCLUDE the filter hidden cells of the formula sheet. Formula sheet is the tab which has the formula.

Regards,

Amit Tandon

www.excelanytime.com

Sorry this didn't help.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

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’sSplit 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!

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.

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!
Sours: https://www.extendoffice.com/documents/excel/2519-excel-countif-filtered-data.html

COUNTIF and COUNTIFS with SUBTOTAL

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

Registered User

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

Registered User

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

Registered User

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

Registered User

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

Originally Posted by JFactor

=(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?

Try this...

=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

Registered User

Re: COUNTIF and COUNTIFS with SUBTOTAL

Excellent. Thanks Tony Valko, that formula works beautifully.

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

• Sours: https://www.excelforum.com/

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
A2:B11 = data
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),--(B2:B11="A"))
--
Biff
Microsoft Excel MVP

Data G26:T818
Thanks
ASP.NET- How to Raise Custom Events from a UserControl

Sours: https://microsoft.public.excel.worksheet.functions.narkive.com/JAUog67h/subtotal-and-countif-or-sumif-combination

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.

Sours: https://exceljet.net/formula/count-visible-rows-only-with-criteria

944 945 946 947 948