CountIF formula is driving me crazy! can someone advise me?

[stephenohear]

Hi all, i'm working on a works project but cant get my head around a problem. in the Training, sick, working and holiday cells, where I want to count the number of staff I use the below formula, which al works well. However, when I filter the regions into Scotland or another region, the countIF function still counts the hidden cells. For example, if I filter for Ireland, the Working number should change to 4.
Does anyone who is more skilled than I know how to get round this? Would appreciate any advice.
I cant see how the subtotal function would work here.

=COUNTIF(D8:D21,"W")
[TABLE="class: grid, width: 839"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Training
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Sick
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[TD]13
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Holiday
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Day
[/TD]
[TD]Monday
[/TD]
[TD]Tuesday
[/TD]
[TD]Wednesday
[/TD]
[TD]Thursday
[/TD]
[TD]Friday
[/TD]
[TD]Saturday
[/TD]
[TD]Sunday
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Date
[/TD]
[TD]28/08/2017
[/TD]
[TD]29/08/2017
[/TD]
[TD]30/08/2017
[/TD]
[TD]31/08/2017
[/TD]
[TD]01/09/2017
[/TD]
[TD]02/09/2017
[/TD]
[TD]03/09/2017
[/TD]
[/TR]
[TR]
[TD]Region
[/TD]
[TD]Engineer Name
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Scotland
[/TD]
[TD]Tom Smith
[/TD]
[TD][/TD]
[TD]H
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[/TR]
[TR]
[TD]Scotland
[/TD]
[TD]William Peterson
[/TD]
[TD][/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]R
[/TD]
[TD]R
[/TD]
[/TR]
[TR]
[TD]Scotland
[/TD]
[TD]James Smith
[/TD]
[TD][/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]R
[/TD]
[TD]R
[/TD]
[/TR]
[TR]
[TD]Scotland
[/TD]
[TD]Frank Smith
[/TD]
[TD][/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]R
[/TD]
[TD]R
[/TD]
[/TR]
[TR]
[TD]Scotland
[/TD]
[TD]David Smith
[/TD]
[TD][/TD]
[TD]T
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]S
[/TD]
[TD]W
[/TD]
[TD]R
[/TD]
[TD]R
[/TD]
[/TR]
[TR]
[TD]Scotland
[/TD]
[TD]Fred Blogs
[/TD]
[TD][/TD]
[TD]W
[/TD]
[TD]S
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]R
[/TD]
[TD]R
[/TD]
[/TR]
[TR]
[TD]North England
[/TD]
[TD]John Doe
[/TD]
[TD][/TD]
[TD]S
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[/TR]
[TR]
[TD]North England
[/TD]
[TD]Peter Williams
[/TD]
[TD][/TD]
[TD]W
[/TD]
[TD]S
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]R
[/TD]
[TD]R
[/TD]
[/TR]
[TR]
[TD]North England
[/TD]
[TD]Stephen Franklin
[/TD]
[TD][/TD]
[TD]T
[/TD]
[TD]T
[/TD]
[TD]T
[/TD]
[TD]T
[/TD]
[TD]H
[/TD]
[TD]R
[/TD]
[TD]R
[/TD]
[/TR]
[TR]
[TD]North England
[/TD]
[TD]James Blogs
[/TD]
[TD][/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]R
[/TD]
[TD]R
[/TD]
[/TR]
[TR]
[TD]Ireland
[/TD]
[TD]Jane Blogs
[/TD]
[TD][/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]R
[/TD]
[TD]R
[/TD]
[/TR]
[TR]
[TD]Ireland
[/TD]
[TD]Bob Smith
[/TD]
[TD][/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]R
[/TD]
[TD]R
[/TD]
[/TR]
[TR]
[TD]Ireland
[/TD]
[TD]Jane Doe
[/TD]
[TD][/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[/TR]
[TR]
[TD]Ireland
[/TD]
[TD]Frank Flaherty
[/TD]
[TD][/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]W
[/TD]
[TD]R
[/TD]
[TD]R
[/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

If, instead of using a filter, you put the region in a cell (I used A6), you could change to countifS() for this
(I modified your original formula a bit to make it easier to copy)
=COUNTIFS(D$8:D$21,LEFT($C3,1),$A$8:$A$21,$A$6)
copied down and across as needed

Just change the country in A6 (or make that a DD, too)

 

[FDibbins]

This will do it the way you want, using Filters...
D1=SUMPRODUCT(SUBTOTAL(3,OFFSET($A$8,ROW($A$8:$A$21)-ROW($A$8),)),--(D$8:D$21=LEFT($C1,1)))
copied down and across as needed
(base formula courtesy of benishiryo)

 

[stephenohear]

Absolutely brilliant, copied in formula, checked results, worked a treat. I tip my hat to you Ford. Thank you for sharing the knowledge!