How to calculate totals on 1st of each month

Hi

This is my first post. I'm quite proficient with Excel but have never learnt Visual Basic.

I have a workbook with two sheets. One contains a number of items of data, the other some totals. The data sheet contains a column that has a yes or no option.

The totals sheet totals data by month. I want the sheet to record on the 1st of each month the total number of No's in column mentioned.

I have a formula =if(today()=date(year(B1),month(B1),1)countif(sheet3!F:F,"No"))

This works but only on the 1st of each month and previous months data obviously disappears.

Is there a way to do what I want with a formula or do I need a macro that runs on 1st of each month which contains similar cose to my countif formula?

If a macro is needed, can anyone give me a hand? As I said I don't know VBA.

Thanks

Tom

Hi

Thanks for looking. Couldn't post an example earlier as work computer has Firewall that blocked me logging into the site and was posting from my phone.

So this is the totals sheet.

and this is the data sheet

I want row 16 (total ongoing at time) to count the number of No's in Column F (Closed). But I want it to count the number of No's on the 1st of each month so can see if this is reducing or increasing. We don't have a column for date closed, as I'm typing this I am thinking that this would solve the problem as I could just count the dates closed for a specific month. I wonder if I could have an extra column to record this that would automatically record the date that someone changes a yes to a no

Thanks again for looking,

Tom