Continuing from my previous post, showing my process in designing a spreadsheet.
Here Iβve placed each Day/Date heading over 3 columns (later I figured out I needed 4). Itsβ starting to look a little bit like a calendar.
One of the things making the design of this calendar a little more challenging is that each day has a different number of posts. Anywhere between 2-20 depending on the days activities (library author quizzes, anyone?). So my formula needs to account for a variable number of entries each day, I can’t simply copy 20 lines and call it done. So, how will I do that?
I’ve put the following formula into B5, it will find the row that each days’ entries begin on:
=MATCH(B$4,Content!$E1:$E1187,0)
Here the MATCH function does the trick of looking on the Content sheet (where my database lives) and counting down the rows to find the date that matches, then it returns the value of the rows counted.
In C5 I’m using the following formula to count the number of entries on that date:
=COUNTIFS(Content!$E$1:$E$1187,”>=”&B$4,Content!$E$1:$E$1187,”<“&B$4+1)
The reasoning behind using COUNTIFS this way is because when dates are entered in the content sheet, sometimes a time is entered as well. The time remains unseen because of the formatting applied to the date column. I need to ensure I get every entry regardless of whether a time is present or not. Using COUNTIFS to count in a range will do this.
Iβll continue with the design process in my next post.
I offer Excel template design services and training. Feel free to send me an email.