Create a dynamic named range based on Filter selection from Table -m Help Needed

Hi All

I was wondering whether someone can help me with the following problem.

I have a worksheet called Names, which has the names and comments and department. All these are in a table called table 2. I have a formula which counts the number of cells based on their colour, so it count cells that are red, or green or Grey

My named range currently is

Please Login or Register  to view this content.

What I need is when I chose Dept A from my Filter on the table then the named range just takes into account what is shown on the screen, so I can use my formula to count the number of red cells for the chosen department. However this doesn't work properly , the formula for the named range works but it doesn't assign the rows which are shown according to my filter so my count function doesn't return the correct answer.



I hope this is clear


Any help is appreciate it