COUNT formula selectively working?

I made an attendance schedule for a nursery, it's supposed to allow me to check who is in what class depending on the month selected in the dropdown menus in A1/B1.
Zoomed in table.png


Then at the bottom is another table that counts how many kids are in each class, each day.

For some reason whenever i select a date in the past, the COUNT formula in the bottom table stops returning values... except for Thursday and Friday - despite them having an identical formula to the other days.
Zoomed in for August.png

The formula is as follows
=COUNT(FILTER(Attendance[Thursday], (Attendance[Thursday]=1) * SUBTOTAL(103, OFFSET(Attendance[Thursday], ROW(Attendance[Thursday])-MIN(ROW(Attendance[Thursday])),,1))))

Can anyone see where i've gone wrong here?

Attendance for Excel.xlsm

(Also, if anyone has any ideas/templates on how i could streamline any of this process please share! I've worked so long on this sheet that i can't see anything anymore)

Thanks, your version does seem to work!
My previous formula was trying to prevent excel from counting rows that were filtered out in Column N. But your formula does this much more succinctly.

Originally Posted by 6StringJazzer

The first thing that happened when I opened your file is that row 5 for A W was all errors because the DOB is text, not a valid date value. I don't know why your screenshot does not show the errors. The "07" is causing it to be interpreted as text. I removed the extra 0 in my version.

Attachment 876843


I am having trouble reverse engineering the formula you have in the bottom table. It seems overly complicated to simply return the number of kids in each class, each day. I would say the whole scheme is more complicated than necessary. You are coding the presence on each day from 1-3 depending on the class, but the class is already available in column F. I see you are using that to make the conditional formatting colors match the cell fill used for each class, but it's not necessary to use that information in formulas.

I have created a copy of your sheet with the date correction and a new formula for the bottom table that appears to work. I did not take the time to break down your formula to figure out why it wasn't working.

Thanks this worked, and yes as someone mentioned, it's because i use the british date format

By the way, your formula for Age and Class could be shortened to:

Age in E4: =FLOOR(YEARFRAC([@DOB],DATEVALUE($A$1&$B$1)),0.5)
Class in F4: =LOOKUP([@Age],{0,1.5,3},{"Lambs","Builders","Beavers"})