Aug 18 2024 04:03 PM
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
Aug 18 2024 05:46 PM
Take a look at the FILTER function. It can be used to filter a database with multiple criteria. It can be nested in a COUNT function that counts the number of rows that were produced by FILTER. This YouTube video also serves as a helpful introduction to FILTER.
If you still need more help, let me recommend that you post a copy of your existing workbook on OneDrive or GoogleDrive and paste a link here that grants edit access.
Aug 19 2024 05:03 AM
Thank you @mathetes I really appreaciate you taking the time to point me in the right direction.
Aug 19 2024 07:16 AM - edited Aug 19 2024 07:18 AM
Let us know if FILTER was what you needed, i.e., did it work? I know that I've found it to be one of the most useful of functions ever since it was released, in particular because it can solve precisely the quandary you found yourself in, a need for IF (and related functions like SUMIF and COUNTIF) to be used in conjunction with numerous criteria.
It also comes in handy in sifting and sorting through databases for other purposes.
So enjoy that tool!