Subtotal and Countif

Hi,. I've searched this and other forums to try to find a solution to my problem but cant find one that I can make sense of and transfer it to my worksheet, so I'm hoping somebody will be able to help.

On tab 1 of my workbook I have dates of employee training and dates when this training is due for renewal. This can be filtered into different staff groups. On tab two I have statistical data calculated from the info on tab 1. I need a formula on tab 2 to count the number of staff due to renew training, which I have done by using the formula

=COUNTIF('Training Records'!G6:G173,"<"&$K$1)

i.e. count if the renewal due date is prior to today ('Training Records being the name of tab 1, and K1 being todays date). My problem is that when tab 1 has been filtered to a specific staff group, I need this to only count those in the filtered list but cant work out a way to do this as SUBTOTAL doesnt work with COUNTIF.

I hope that makes sense! Thanks for any help you can give me.

Hi, sorry I dont understand what you mean? I cant find a way to use subtotal with countif.

I've attached a small sample of the workbook with all confidential info removed. So its the formula in column D on the Statistical Report tab that I need help with. It refers to the training records tab which would normally be filtered by the Organisation Unit column, e.g. South Central. I need the formula to only count if the date is prior to today, and only for the filtered staff.

Thanks very much.

Originally Posted by Tony Valko

I didn't download your file.

Sounds like you want something like this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET('Training Records'!G6,ROW('Training Records'!G6:G173)-ROW('Training Records'!G6),0)),--('Training Records'!G6:G173<$K$1))

Tonky Valko you are a star! That works perfectly! Thank you very much.