How to Count Rows from Another Filtered Worksheet that Meet Certain Criteria

Hello, I have a table that is currently counting items from another worksheet using COUNTIFS. It works great except it is counting all the rows from the other worksheet, which has been filtered. I need it to count only the items showing in the filtered worksheet.

I know that SUBTOTAL is probably the best route to go, however I can't figure out how to write the correct formula to count only items that meet the specified criteria in my COUNTIFS formula.

Worksheet: Status Summaries
Current Formula in table: =COUNTIFS('Key Issue Details'!$C$2:$C$1048576,PickList!$A$18,'Key Issue Details'!$D$2:$D$1048576,PickList!$A2)+COUNTIFS('Key Issue Details'!$C$2:$C$1048576,PickList!$A$19,'Key Issue Details'!$D$2:$D$1048576,PickList!$A2)

Any guidance would be much appreciated!

Hi Lenny,

The formula below should work. It counts the number of items meeting the specified criteria from the filtered range.

Please Login or Register  to view this content.

General Formula

Please Login or Register  to view this content.

Below is the part of the formula that counts the number of items meet a specified criteria.

Please Login or Register  to view this content.

*Evaluated alone the above formula results in a single (1 Dimensional) array of TRUEs & FALSEs

Multiple Conditions (And) are determined using multiplication.

Please Login or Register  to view this content.

*When the arrays of TRUEs & FALSEs are multiplied by one another TRUEs become 1s and FALSEs become 0s. This results in a single array of 1s & 0s, where 1s indicate only the items that meet all the specified criteria.

OR Conditions are determined using addition.

Please Login or Register  to view this content.

*When the arrays of TRUEs & FALSEs are added by one another TRUEs become 1s and FALSEs become 0s. This once again results in a single array of 2s, 1s, & 0s, where >0 indicate all items that meet at least one of the specified criteria. If it possible to meet both of the OR conditions, then additional logic, such as --((condition formula)>0), is required in order to return the correct result.

Formula below determines visible/filtered rows

Please Login or Register  to view this content.

Subtotal COUNT Function Options:

3 - includes filtered rows & manually hidden rows,

103 - includes only filtered rows

The section of the formula below returns a single array composed of individual cells references (A1,A2,A3, etc...) when is this used as the cell references in the subtotal function. Each individual cell reference is evaluated to determine if it is visible. This results in a 1 dimensional array of 1s & 0s.

Please Login or Register  to view this content.

Finally, your criteria array is multiplied by your filtered/visible rows array to determine the number items in filtered range that meet your specified criteria.

I would also not recommend using full column references in the formula as it drastically increases the processing time/power required for each recalculation. I would either use a table, named range, or absolute referenced range specified.



DMG

Thank you so much for your response DMG, and for including a fantastic breakdown of the formula! It works perfectly!