Filter within a filter?

Hello,

I'm no excel guru, and the attached spreadsheet continues to evolve using the things that I am learning from this forum. I'm hoping to get a little more help with filters. The spreadsheet is normally used on a Windows 7 machine and MS Office 2010.

This spreadsheet is a tool that I use to track training that is due every 12 months, and every 24 months. The cell colors are based on the date:

green = training was completed within 12/24 months
yellow = training is due in less than 30 days
red = training is overdue

The problem:
With all of the filters turned off you can see there are only 5 people who are current on their "Drug Test". ROW 11 and ROW 12 are accurate according to the data shown.

Now use the "Team" filter and checkmark only the Seahawks. The spreadsheet correctly shows two names, but the data in ROWs 10-15 never change. This is the same for all the columns under "Annual" and "24 months", and for every filter.

I have had zero luck understanding how to modify the COUNTIFS. Can someone help me fix this issue?

Thank you in advance.

welcome to the forum, Keyboard Ninja. to work with autofilter, SUBTOTAL will help you. in A10:
="Total Names: "&SUBTOTAL(3,A3:A8)

C11:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C3,ROW(C3:C8)-ROW(C3),))*(C3:C8>TODAY()-365))

the rest are pretty similar

C12:
=(C11/SUBTOTAL(3,$A$3:$A$8))

you may read more about the SUMPRODUCT method explained in the file of my link below*Tips & Tutorials I Compiled, sheet 10

Originally Posted by benishiryo

welcome to the forum, Keyboard Ninja. to work with autofilter, SUBTOTAL will help you. in A10:
="Total Names: "&SUBTOTAL(3,A3:A8)

Hi,

Thank you for the reply.

When I use your formula for A10 I get a FALSE. Am I missing something?



Edit: Nevermind. It was a typo on my part. Your formula worked great. Thanks!

C11-C14 work great. Thank you!

Also tried using this for cell D15 (and other cells in that row) with no luck.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D3,ROW(D3:D8)-ROW(D3),))*(D3:D8))

try removing the last argument...
=SUMPRODUCT(SUBTOTAL(3,OFFSET(D3,ROW(D3:D8)-ROW(D3),)))

Format as General

I tested it on your data and it worked fine?

	D
2	[BGCOLOR=#FFFFFF]Domestic Abuse[/BGCOLOR]
3	02-Apr-15
4	25-Sep-15
5	03-Aug-15
6	13-Oct-15
7	14-Jul-13
8	29-Sep-15
9
10
11	5
12	83%
13	0
14	1
15	0
16	6
17

	D
2	[BGCOLOR=#FFFFFF]Domestic Abuse[/BGCOLOR]
3	02-Apr-15
4	25-Sep-15
5	03-Aug-15
6	13-Oct-15
7	14-Jul-13
8	29-Sep-15
9
10
11	5
12	83%
13	0
14	1
15	0
16	5
17

edit: OK so teh copy/paste for the 2nd table did not turn out so good - I had filtered on only 2015

hmmm this one got me stumped. have asked for help. very interested in knowing a solution too!

If you will consider a helper column try this. In M3:M8 (masked out by black formatting) this formula.

Formula:

Please Login or Register  to view this content.

Then in C11:G11.

Formula:

Please Login or Register  to view this content.

In H11:L11.

Formula:

Please Login or Register  to view this content.

And in C12 and fill across.

Formula:

Please Login or Register  to view this content.

In C15:L15.

Formula:

Please Login or Register  to view this content.

Now try filtering.
If this does what you want then continue to include the helper column in the other COUNTIF(s) formulas in the remaining rows / columns.

Does this do what you want?