Countif ignoring blanks weird table

[indygo]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[TD]Friday[/TD]
[TD]Saturday[/TD]
[TD]Sunday[/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]2[/TD]
[TD]John[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]David[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Eve[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Name[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[TD]Friday[/TD]
[TD]Saturday[/TD]
[TD]Sunday[/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]7[/TD]
[TD]John[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]OFF[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]OFF[/TD]
[TD]8 AM[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Rose[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Eve[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]8 AM[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I have this badly designed table and I'm trying to calculate how many times people were off on a certain day of the week.
Countif struggles with blank rows (row 5) or ranges which are not equal.

So I want to create a table Monday to Sunday and calculate how many times employees were off on certain days. Note that this schedule is based on 2weeks range (B-F)



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/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]Eve[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks

 

[Velven]

Hello Indygo

I shall be assuming your Eve is at A12
Fill in this formula at B12:H12
=COUNTIFS($B$4:$O$4,"Off",$B$1:$O$1,B$11)+COUNTIFS($B$9:$O$9,"Off",$B$6:$O$6,B$11)

We'll be using 2 countifs formula

 

[indygo]

Thanks for response,

But you see, if I have 4 years of data I can't attach this formula separately to each name because it would take ages.
So I was hoping I could use countifs and just refer to names in that formula to calculate them automatically.
Your formula forces me to apply it manually to each row with name "Eve" manually.