Hello there, 

I am self taught using excel and as a result I can do some tricks quite well but have gaps with even some of the most basic functionality. I also struggle a little with a lot of the terminology so please bear with me. Apologies. 

I am trying to build a spreadsheet for a nursery that will count the number of children booked to attend a particular time slot on a particular day in the future. 

I have one tab holding all of the individual child records and all of the data pertaining to them across a row. Name, date started, date finished, nursery, room, term, monday block 1, Monday block 2 etc. 

Where a child is scheduled to be in nursery for a particular block I enter a “1”, if they are not scheduled to be there I leave the cell blank. 

This is set up as a table. 

I am trying to build a seperate tab which will examine that data tab and summarise the number of children scheduled to be attendant based on a dynamic, user specified date, nursery and room. 

So what I would like to do is count the number of rows that meet the following criteria

1 – Where the chosen date is after the child’s start date

2 – Where the chosen date is before the child’s end date

3 – Where the chosen nursery is the same as the child’s nursery 

4 – Where the chosen room is the same as the child’s room

I thought I could do this with a countifs but when I try to do that, the cells below my formula cell autocomplete with the same number of rows as are in my data table. I would simply like the cell to have a single number reflecting the nuber of children who meet the criteria specified…….if that makes sense. 

I’m not sure if I’m just being an idiot, tryng to do too much in one go, simply misunderstanding the formula I should be using or a combination of all of these things. 

Any advice or pointers would be greatly appreciated. 

Regards,

Joe

​Hello there,  I am self taught using excel and as a result I can do some tricks quite well but have gaps with even some of the most basic functionality. I also struggle a little with a lot of the terminology so please bear with me. Apologies.  I am trying to build a spreadsheet for a nursery that will count the number of children booked to attend a particular time slot on a particular day in the future.  I have one tab holding all of the individual child records and all of the data pertaining to them across a row. Name, date started, date finished, nursery, room, term, monday block 1, Monday block 2 etc. Where a child is scheduled to be in nursery for a particular block I enter a “1”, if they are not scheduled to be there I leave the cell blank. This is set up as a table.  I am trying to build a seperate tab which will examine that data tab and summarise the number of children scheduled to be attendant based on a dynamic, user specified date, nursery and room.  So what I would like to do is count the number of rows that meet the following criteria 1 – Where the chosen date is after the child’s start date2 – Where the chosen date is before the child’s end date3 – Where the chosen nursery is the same as the child’s nursery 4 – Where the chosen room is the same as the child’s room I thought I could do this with a countifs but when I try to do that, the cells below my formula cell autocomplete with the same number of rows as are in my data table. I would simply like the cell to have a single number reflecting the nuber of children who meet the criteria specified…….if that makes sense. I’m not sure if I’m just being an idiot, tryng to do too much in one go, simply misunderstanding the formula I should be using or a combination of all of these things.  Any advice or pointers would be greatly appreciated.  Regards, Joe  Read More

​